Difference between DATEPART and DATENAME in SQL

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

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

Difference between DATEPART and DATENAME in SQL 1

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 SQL DATENAME function to display the Month name and weekday name from the given date. It returns Month name as September and Weekday as Tuesday

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

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

We asked the SQL DATEPART function to display the Month number and weekday number from the given date. It will return Month number as 9 and 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 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 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 adding (9 + 1 = 10)

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

Difference between DATEPART and DATENAME in SQL 3

Let us extract 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 SQL Datename function to display the year name from the given date and added character a to it. The following statement will do string concatenation

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

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

SELECT DATEPART(month, @Date) + 'a' AS [DatePart] 
Difference between DATEPART and DATENAME in SQL 4