CODEWITHSUNDEEP
sql-server
user1077685
user1077685

Reputation:

Format date as Month dd, yyyy?

In SQL Server, I'd like to select a date from a DateTime column in Month dd, yyyy format.

I currently am using the following code:

SELECT CONVERT(VARCHAR(12), DateColumnHere, 107) 'Formatted Date'

Which returns:

Feb 15, 2013

Is there any way to get the full month name, like:

February 15, 2013

Thanks!

Upvotes: 0

Views: 26946

Answers (4)

Gaston Flores
Gaston Flores

Reputation: 2467

Try with this:

SELECT STUFF(CONVERT(VARCHAR(12), GETDATE(), 107), 1,3,DATENAME(month,GETDATE()))

Upvotes: 1

D Stanley
D Stanley

Reputation: 152521

SQL Server has a limited number of date formats, but you can use the DATENAME function to concatenate the peices:

SELECT DATENAME(MM, GETDATE()) + ' ' + 
       DATENAME(DD, GETDATE()) + ', ' + 
       DATENAME(YYYY, GETDATE())
       AS 'Formatted Date'

However in general it's often better practice to leave dates in date format rather than converting to strings, and only converting to strings in the display layer (Web site, report, application, wherever) where the formatting capabilities are much richer. Plus you lose the ability to do date math, sorting, etc. if you convert to string.

Upvotes: 2

valverij
valverij

Reputation: 4941

Here is a site with a ton of formats and little date hacks for SQL:

http://www.sql-server-helper.com/tips/date-formats.aspx

And here is their query for your format (Month dd, yyy):

SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

Upvotes: 5

DoctorMick
DoctorMick

Reputation: 6793

SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

Upvotes: 1

Related Questions