Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 802
  • Last Modified:

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!
0
hyphenpipe
Asked:
hyphenpipe
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
chapmandewCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 CrossChief 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now