Sunday, January 9, 2011

DATEPART returns three character month names, date and year.

Just a quick way to CASE the output of the current date, to include the Month Name, as opposed to the numeric digit.  Today is 1/9/2011, this is my result:

    Date
    Jan 9, 2011

SELECT 'Date' = 
CASE
WHEN (DATEPART(MONTH,GETDATE()) = 1 ) THEN 'Jan'
WHEN (DATEPART(MONTH,GETDATE()) = 2 ) THEN 'Feb'
WHEN (DATEPART(MONTH,GETDATE()) = 3 ) THEN 'Mar'
WHEN (DATEPART(MONTH,GETDATE()) = 4 ) THEN 'Apr'
WHEN (DATEPART(MONTH,GETDATE()) = 5 ) THEN 'May'
WHEN (DATEPART(MONTH,GETDATE()) = 6 ) THEN 'Jun'
WHEN (DATEPART(MONTH,GETDATE()) = 7 ) THEN 'Jul'
WHEN (DATEPART(MONTH,GETDATE()) = 8 ) THEN 'Aug'
WHEN (DATEPART(MONTH,GETDATE()) = 9 ) THEN 'Sep'
WHEN (DATEPART(MONTH,GETDATE()) = 10 ) THEN 'Oct'
WHEN (DATEPART(MONTH,GETDATE()) = 11 ) THEN 'Nov'
WHEN (DATEPART(MONTH,GETDATE()) = 12 ) THEN 'Dec'
END + ' ' + DATENAME(dd,GETDATE()) + ', '+ DATENAME(yyyy,GETDATE())

2 comments:

  1. The same can be done like this:
    SELECT Date = LEFT(DATENAME( m, GETDATE()),3) + ' ' + DATENAME(dd,GETDATE()) + ', ' + DATENAME(yyyy,GETDATE())

    ReplyDelete
  2. ALSO

    SELECT FORMAT([Date],'MMM') AS [Short Month Name]

    ReplyDelete