Format date in SQL query like (Month Day, Year)

I am sure I must be just missing something, but what is the simplest way to output a datetime field to look like this:

May 5, 2009

when currently the field value is equal to this:

2009-05-05 13:05:03.530

It doesn't have to appear in the database in that format, just in the output of select statement.

Thanks!
LVL 6
hyphenpipeAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select convert(varchar(10), getdate(), 101)
0
 
chapmandewCommented:
from a table:

select convert(varchar(10), FIELDNAME, 101)
from tablename
0
 
chapmandewCommented:
swap out 101 for 100...
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
select convert(varchar(10), FIELDNAME, 100)
from tablename
0
 
Kevin CrossChief Technology OfficerCommented:
Also:
SELECT CONVERT(VARCHAR, GETDATE(), 107)

See reference:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
RiteshShahCommented:
this is all about datetime conversion game, you can see all available date time in SQL Server with very small script, have a look at my small article.

http://www.sqlhub.com/2009/04/list-of-all-available-datetime-format.html
0
 
hyphenpipeAuthor Commented:
I apologize for my error.  Although these are close (I am already doing something similar), I should have used a better example other than the month of May.

Is it possible to get the full month string as part of the date, i.e. December 7, 1941 rather than Dec 7 1941 without a case statement?
0
 
chapmandewCommented:
Not too easily...however,  Iwould suggest using a dates table, as I use in this article

http://blogs.techrepublic.com.com/datacenter/?p=326
0
 
Kevin CrossChief Technology OfficerCommented:
Try like this:
DECLARE @date DATETIME
SET @date = GETDATE()-28;
 
SELECT DATENAME(month, @date) + ' ' + SUBSTRING(CONVERT(VARCHAR, @date, 107), 5, 8)

Open in new window

0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Or use the original space from conversion in 107.
SELECT DATENAME(month, @date) + SUBSTRING(CONVERT(VARCHAR, @date, 107), 4, 8)

Open in new window

0
 
hyphenpipeAuthor Commented:
I awarded 250 to chapmandew and 250 to mwvisa1.  I hope this is satisfactory.  Thanks for the help!
0
 
Kevin CrossChief Technology OfficerCommented:
hyphenpipe,

Glad to help.

Regards,
Kevin

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.