• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Converting Dates with SQL

I know I've done this before but can't remember

How do I convert a date so it comes out in long day, month year format in t-sql

for example

1/17/2013

to

Monday, January 7, 2013
0
johnnyg123
Asked:
johnnyg123
1 Solution
 
Kent DyerCommented:
CAST / CONVERT in BOL should cover this..

http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.105%29.aspx

HTH,

Kent
0
 
Steve WalesSenior Database AdministratorCommented:
Cast/Convert don't seem to translate the date format into words.

You need the DATENAME function to do that.

Here's a (somewhat ugly) way to handle it but shows how to extract the words from a datetime parameter:

select datename(dw,getdate())+', '+
       datename(mm,getdate())+' '+
       rtrim(convert(char,datepart(dd,getdate())))+', '+
       rtrim(convert(char,datepart(yyyy, getdate())))

Monday, January 7, 2013

Open in new window


DATEPART: http://msdn.microsoft.com/en-us/library/ms174420.aspx
DATENAME: http://msdn.microsoft.com/en-us/library/ms174395.aspx
0
 
CrashmanCommented:
take a look, contains all conversions

http://www.sqlusa.com/bestpractices/datetimeconversion/
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Alpesh PatelAssistant ConsultantCommented:
select Format (sysdatetime(), 'D', 'en-US')
0
 
ValentinoVBI ConsultantCommented:
PatelAlpesh, the FORMAT function only exists in SQL Server 2012.  The question clearly states that the asker uses SQL Server 2008.  Please read the full question and other posts before deciding to post yourself. (this is not the first time)

The answer by sjwales achieves what has been asked.
0
 
Anthony PerkinsCommented:
this is not the first time
And unfortunately it will not be the last, despite repeated reminder over the years.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now