Difference Between DATENAME and DATEPART in SQL SERVER

Most of the time we get requirement to fetch some part of date in SQL Server. Suppose we have date value '10/25/2015' and we want to fetch year(2015) only then we use Datepart() function to accomplish this. We have another function Datename() which we can use to extract information from date.

 

For more on Datetime functions, visit here.

 

In this blog we will discuss what is the difference between them and what we need to use them.

 

DATEPART

It is a Datetime function which helps to extract information from date. This function always returns result as integer type.

 

DATENAME

It is also another Datetime function which helps to extract information from date. This function always returns result as nvarchar(character string).

 

Both the functions are available from SQL Server 2005 onwards. 

 

Analysis

 

Let's analyse the following figures to understand it better.

 

In Figure 1, we used two functions Datepart() and Datename() to fetch year from current date. We got same result(2015) for both the queries.

 

Figure 1: Same result for Datepart and Datename

 

In Figure 2, we used two functions Datepart() and Datename() to fetch month from current date. We got different result - 'August' for Datename() and '8' for Datepart. It means Datename returns in character format. It's result differs when query fetches month, weekday, etc. Here Datepart returns 8 as a result, it means Datepart always returns in integer format.

 

 
Figure 2:  Different result for Datepart and Datename

 

In Figure 3, we used two functions Datepart() and Datename() to fetch year from current date and appending 1. We got different result.

Datename result is '20151' -> DATENAME(YEAR,GETDATE()) returns '2015' in character format. After appending '1', result is '20151'

Datepart result is 2016 -> DATEPART(YEAR,GETDATE()) returns 2015 in integer format. After appending '1' result is '2016'. 

 

 
Figure 3: Different result by using same  query as Figure 1 with appending value '1'

 

Conclusion

 

From above discussions, sometimes both functions returns same result and sometimes different. So if you need result as character string format, month name, weekday name then DATENAME is the best. On the other hand if you need result as integer format and also operations add, subtract with result, then DATEPART is the best.

 

Hope it helps you to understand the difference between DATEPART and DATENAME in SQL SERVER.

 

Happy Coding!!