Datename Function in SQL Server 2012

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:

 

 

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

MONTH: Returns the month as an integer.

Example

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

Output

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

Example

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

Output

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

Hour: Returns the hour value as an integer.

Example

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

Output

Minute: Returns the minute value as an integer.

Example

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

Output

Second: Returns the Second value as an integer.

Example

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

Output

WeekDay: Returns the WeekDay name as a String.

Example

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

Output

Week: Returns the Week value as an integer.

Example

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

Output

Dayofyear: Returns the Dayofyear name as an integer.

Example

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

Output