Solved

Converting Dates with SQL

Posted on 2013-01-07
6
165 Views
Last Modified: 2013-01-24
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
Comment
Question by:johnnyg123
6 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 38752380
CAST / CONVERT in BOL should cover this..

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

HTH,

Kent
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38752418
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
 
LVL 8

Expert Comment

by:Crashman
ID: 38752462
take a look, contains all conversions

http://www.sqlusa.com/bestpractices/datetimeconversion/
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38753587
select Format (sysdatetime(), 'D', 'en-US')
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38753626
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38761568
this is not the first time
And unfortunately it will not be the last, despite repeated reminder over the years.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now