Difference between DATEPART and DATENAME in SQL

The difference between DATEPART and DATENAME in SQL is one of the most commonly asked interview questions. Although the syntax of the SQL Server DATEPART and DATENAME is the same, they differ in their return value.

The fundamental difference between them is the SQL DATEPART function return integer value, and the DATENAME function return String data.

Difference between DATEPART and DATENAME in SQL

In this example, we are going to declare a variable of datetime2 data type and assign a valid date to that variable. Using that variable, Let us extract month and Weekday using DATEPART and DATENAME

DECLARE @Date datetime2 = '2015-09-01 12:24:04.1234567'

SELECT DATENAME(month, @Date) AS [DateName] 
SELECT DATEPART(month, @Date) AS [DatePart] 

SELECT DATENAME(weekday, @Date) AS [DateName] 
SELECT DATEPART(weekday, @Date) AS [DatePart]

Difference between DATEPART and DATENAME in SQL 1

We declared one SQL variable and assigned the date and time to that variable

DECLARE @Date datetime2 = '2015-09-01 12:24:04.1234567'

We asked the DATENAME function to display the Month name and weekday name from the given date. It returns the Month name as September and Weekday as Tuesday

SELECT DATENAME(month, @Date) AS [DateName]

SELECT DATENAME(weekday, @Date) AS [DateName]

We asked the DATEPART function to display the Month number and weekday number from the given date. It will return the Month number as 9 and the weekday number as 3 (o= Sunday)

SELECT DATEPART(month, @Date) AS [DatePart] 

SELECT DATEPART(weekday, @Date) AS [DatePart]

Difference between DATEPART and DATENAME in SQL 2

In this example, we are going to declare a variable of datetime2 data type and assign a valid date to that variable. Using that variable, Let us extract the month using DATEPART and DATENAME and perform arithmetic addition.

DECLARE @Date datetime2 = '2015-09-01 12:24:04.1234567'

SELECT DATENAME(month, @Date) + '1' AS [DateName] 
SELECT DATEPART(month, @Date) + '1' AS [DatePart]

Difference between DATEPART and DATENAME in SQL 2

We asked the SQL Datename function to display the Month name from the given date and added integer 1 to it. Since the output of a Datename function is a string, the following statement will do string concatenation

SELECT DATENAME(month, @Date) + '1' AS [DateName]

Since the output of a Datepart function is an integer, the following SQL Datepart statement will do arithmetic by adding (9 + 1 = 10)

SELECT DATEPART(month, @Date) AS [DatePart]

Difference between DATEPART and DATENAME in SQL 3

Let us extract the year using DATEPART and DATENAME and append a string to the output

DECLARE @Date datetime2 = '2015-09-01 12:24:04.1234567'

SELECT DATENAME(year, @Date) + 'a' AS [DateName] 
SELECT DATEPART(year, @Date) + 'a' AS [DatePart]

Difference between DATEPART and DATENAME in SQL 3

We asked the Datename function to display the year name from the given date and added the character a to it. The following statement will do string concatenation.

SELECT DATENAME(month, @Date) + 'a' AS [DateName]

Since the output of the Datepart function is an integer, the following statement will throw a below-shown error

SELECT DATEPART(month, @Date) + 'a' AS [DatePart]