Reader Level:
ARTICLE

Datename Function in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server October 01, 2012
Here, you will see the DateName function that represents the specified datepart of the specified date such as day, month, year, quarter, hour, minute in SQL Server.
  • 0
  • 0
  • 7037

Here, you will see the DateName function that represents the specified datepart of the specified date such as day, month, year, quarter, hour and minute in SQL Server. In this article, to define the DateName function we use the GetDate function that returns the current date and time from the SQL Server. After that the DateName function is used to find the date part from the GetDate function. So let's take a look at a practical example of how to use the Datename Function in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Creating Table in SQL Server

CREATE TABLE [dbo].[ProductTable]

(

       [ID] [nchar](10) NULL,

       [ProductName] [varchar](max) NULL,

       [Date] [datetime] NULL

)

 

Now  insert a row into the table, the current date and time is inserted into the column using the GetDate  function.

 

INSERT INTO [TestDatabase].[dbo].[ProductTable] VALUES ('1','Pee Cola', getdate())

go

INSERT INTO [TestDatabase].[dbo].[ProductTable] VALUES ('2','Jarlsberg', getdate())

 

The ProductTable table looks like this:

 

Table-in-Sql-Server.jpg

 

DateName function

 

The DateName function represents the specified datepart of the specified date such as day, month, year, quarter, hour, minute, week and weekday in SQL Server.

SYNTAX

DATENAME ( datepart , date )

DateName function Arguments

Datepart: Datepart is the part of the date to return.

Date: Specifies the date.

To Get Datepart from Date

DAY: Returns the day of the month as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(day, Date) as daynumber from [ProductTable]

Output

Day-Number-in-Sql-Server.jpg

MONTH: Returns the month as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(month, Date) as [Month] from [ProductTable]

Output

Monthr-in-Sql-Server.jpg

YEAR: Returns the 4-digit year as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(YEAR, Date) as [Year] from [ProductTable]

Output

Year-in-Sql-Server.jpg

Quarter: Returns the quarter as an integer (because 1 quarter is equal to 3 months).

Example

SELECT [ID],[ProductName],[Date], DATENAME(Quarter , Date) as [Quarter] from [ProductTable]

OUTPUT

Quater-in-Sql-Server.jpg

Hour: Returns the hour value as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(HOUR , Date) as [HOUR] from [ProductTable]

Output

Hour-in-Sql-Server.jpg

Minute: Returns the minute value as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(MINUTE , Date) as [MINUTE] from [ProductTable]

Output

Minute-in-Sql-Server.jpg

Second: Returns the Second value as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(Second , Date) as [Second] from [ProductTable]

Output

Second-Number-in-Sql-Server.jpg

WeekDay: Returns the WeekDay name as a String.

Example

SELECT [ID],[ProductName],[Date], DATENAME(weekday , Date) as [weekday] from [ProductTable]

Output

Weakdayr-in-Sql-Server.jpg

Week: Returns the Week value as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(week , Date) as [week] from [ProductTable]

Output

Week-in-Sql-Server.jpg

Dayofyear: Returns the Dayofyear name as an integer.

Example

SELECT [ID],[ProductName],[Date], DATENAME(dayofyear , Date) as [dayofyear] from [ProductTable]

Output

Dayofyear-Number-in-Sql-Server.jpg

COMMENT USING

Trending up