Pankaj Tahiliani

  Home  |   Contact  |   Syndication    |   Login
  34 Posts | 0 Stories | 50 Comments | 0 Trackbacks

News

Twitter












Archives

Post Categories

DATENAME function is used to get string of the specified datepart of the specified date.

DATEPART Return of the specified date an integer that represents a specific date

Consider a problem where we have to converting a date to display in the MON YYYY format where "MON" is the 3 character month such as "Jan", "Feb", "Mar", etc.

use NORTHWIND;

SELECT CASE DATEPART(mm,OrderDate) 
             WHEN 01 THEN 'Jan'
             WHEN 02 THEN 'Feb'
             WHEN 03 THEN 'Mar'
             WHEN 04 THEN 'Apr'
             WHEN 05 THEN 'May'
             WHEN 06 THEN 'Jun'
             WHEN 07 THEN 'Jul'
             WHEN 08 THEN 'Aug'
             WHEN 09 THEN 'Sep'
             WHEN 10 THEN 'Oct'
             WHEN 11 THEN 'Nov'
             ELSE 'Dec'
        END + ' ' + CAST(DATEPART(yy,OrderDate) AS CHAR(4))
   FROM Orders

In this example we have cast the results of datepart into character so that we can concatenate both the field results.

Now, in the below example you will see how fast is Datename function in comparison with  Datepart. 

USE NORTHWIND
DECLARE @Lorem CHAR(4);

PRINT '========== Original Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Lorem = CAST(DATEPART(yy,OrderDate) AS CHAR(4)) FROM Orders;
SET STATISTICS TIME OFF;

PRINT '========== DATENAME Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Lorem = DATENAME(yy,OrderDate) FROM Orders
SET STATISTICS TIME OFF; 


That gives us the following statistical output:

========== Original Method of Conversion ==========

SQL Server Execution Times:
CPU time = 1203 ms, elapsed time = 1 ms.
========== DATENAME Method of Conversion ==========

SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 0 ms.



DATENAME() has a rather special effect on days of the week and months. Instead of returning the numeric equivalent as a 2 character NVARCHAR, it actually spells the name out. Try it...
SELECT DATENAME(dw,GETDATE()), DATENAME(mm,GETDATE());

posted on Saturday, July 20, 2013 10:41 PM