?
Solved

Converting Dates with SQL

Posted on 2013-01-07
6
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 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