Temporarily change format of a date column in SQL Query output

Question

I am pulling data from SQL Server, and the date on our back end is formatted prtty ugly, like so:

2014-01-10 00:00:00.000

I was wondering if there is some sort of funciton that I can run in my query to display the date column differently in my output? I don't want to actually alter the way in which the Column is formatted on the back end, I just want to change how it is presented to the user in the results of a sigle query.

Thank you for your help!

Edit: I am using SQL server 2008 R2 as my RDBMS

Duplicate - http://stackoverflow.com/questions/5222075/sql-datetime-format-to-date-only — RacerNerd, Feb 27 '14 at 19:52
What do you want the date to look like? There are many different types of output. — Rick S, Feb 27 '14 at 19:53
Lol I guess I never thought about it? Anything is better than the way it is now. How about the standard MM/DD/YYYY format? — , Feb 27 '14 at 19:56
Alternatively, you can format on the client. If your query is part of an app written in .net, coldfusion, java, etc, they all have date formatting functions. — Dan Bracuk, Feb 27 '14 at 20:00
@AdamJ, your standard is not necessarily other people's standard. Just specify the desired format. — Dan Bracuk, Feb 27 '14 at 20:01

Hart CO · Answer 1 · 2014-02-27T19:58:25.800

1

SQL 2012: Has FORMAT():

SELECT FORMAT(Date_Field,'ddd, MM yyyy')

Here's a list of FORMAT() options

Prior to SQL 2012:

SELECT CONVERT(VARCHAR(12),Date_Field,109)

List of CONVERT() styles

If you just want to remove the time portion you can:

SELECT CAST(Date_Field AS DATE)

edited Feb 27 '14 at 19:58

answered Feb 27 '14 at 19:53

Hart CO

34,064
6
48
63

score 0 · Accepted Answer · answered Feb 27 '14 at 19:57

You can format datetime output using the CONVERT function. Review the MSDN documention and the W3Schools page for the Convert function for more details and examples.

Here is quick example of a few different formats available.

SELECT CONVERT(VARCHAR(19), GETDATE())
SELECT CONVERT(VARCHAR(10), GETDATE(),10)
SELECT CONVERT(VARCHAR(10), GETDATE(),110)
SELECT CONVERT(VARCHAR(11), GETDATE(),106)
SELECT CONVERT(VARCHAR(24), GETDATE(),113)

Results:

Feb 27 2014 11:54AM 
02-27-14
02-27-2014
27 Feb 2014
27 Feb 2014 11:54:33:977

Temporarily change format of a date column in SQL Query output

2 Answers2