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
