SQL Server DATEPART() & DATENAME() Functions

DATEPART()/DATENAME() Date Parts

The DATEPART() and DATENAME() functions are somewhat redundant in terms of what they return. I typically use the DATEPART() function unless I require text translation (example: month). Below is an example of using this function for the major argument available.

SELECT TOP 1
-- Mar 03, 2013  DATE PARTS
    DATEPART(year,GETDATE())            AS "year" 
   ,DATEPART(quarter,GETDATE())         AS "quarter" 
   ,DATENAME(month,GETDATE())           AS "month"
   ,DATEPART(month,GETDATE())           AS "month#"
   ,DATEPART(week,GETDATE())            AS "week"
   ,DATEPART(dayofyear,GETDATE())       AS "dayofyear" 
   ,DATENAME(weekday,GETDATE())         AS "weekday"  
   ,DATEPART(weekday,GETDATE())         AS "weekday#"   
   ,DATEPART(day,GETDATE())             AS "day"  
FROM AdventureWorks2008R2.Sales.Store

DATEPART() Time Parts

Below is an example of using the DATEPART() function for extracting time for the major arguments available.

SELECT TOP 1
-- TIME PARTS
    DATEPART(HOUR,SYSDATETIME())           AS "hour" 
   ,DATEPART(MINUTE,SYSDATETIME())         AS "minute" 
   ,DATEPART(SECOND,SYSDATETIME())         AS "second"
   ,DATEPART(MILLISECOND,SYSDATETIME())    AS "millisecond"
   ,DATEPART(MICROSECOND,SYSDATETIME())    AS "microsecond" 
   ,DATEPART(NANOSECOND,SYSDATETIME())     AS "nonosecond"  --not accurate to fraction 9!   
FROM AdventureWorks2008R2.Sales.Store

 Collapse All    Expand All
Custom Search

SQL Server Information and Resources