Convert a SQL Server datetime to a shorter date format

Question

I have a datetime column in SQL Server that gives me data like this 10/27/2010 12:57:49 pm and I want to query this column but just have SQL Server return the day month and year - eg. 2010 10 27 or something like that.

What are the functions I should be researching?

Should I be trying to convert to another date data type? Or simply convert it to a string?

Which **version** of SQL Server?? SQL Server 2008 introduced a load of new date and time related datatypes, e.g. `DATE` - so if you're on 2008, you can use `SELECT CAST(YourDateTimeColumn AS DATE)` and you get back just the date - no time portion. — marc_s, Jan 17 '12 at 20:25
Take a look at this article: http://msdn.microsoft.com/en-us/library/ms187928.aspx Specifically > Date and Time Styles — Brissles, Jan 17 '12 at 20:21
@marc_s http://meta.stackoverflow.com/questions/314561/are-votes-to-a-question-comment-more-valuable-than-votes-for-an-answer — apaul, Jan 12 '16 at 21:33
Possible duplicate of [How to remove the time portion of a datetime value (SQL Server)?](http://stackoverflow.com/questions/2775/how-to-remove-the-time-portion-of-a-datetime-value-sql-server) — Michael Freidgeim, Aug 31 '16 at 05:47

score 136 · Accepted Answer · edited Aug 06 '14 at 21:22

136

Have a look at CONVERT. The 3rd parameter is the date time style you want to convert to.

e.g.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format

edited Aug 06 '14 at 21:22

NotMe

87,343
27
171
245

answered Jan 17 '12 at 20:20

AdaTheDev

142,592
28
206
200

12

It seems that 106 is "dd mon yy" and "dd/MM/yyyy" is 103 – nahab Dec 07 '12 at 15:30
3

@nahab You are correct. Also, I had to use varchar(11) instead of varchar(10) to prevent the year to be shortened (eg. I saw 195 instead of 1953) – Henrov Jun 03 '13 at 10:31
7

Also, you can use 101 if you want the results formatted mm/dd/yyyy – John Smith Aug 11 '17 at 14:17

score 64 · Answer 2 · edited Feb 19 '14 at 14:29

64

Try this:

print cast(getdate() as date )

edited Feb 19 '14 at 14:29

snollygolly

1,858
2
17
31

answered Feb 19 '14 at 14:09

jabu.hlong

2,194
20
21

5

I like this option more than the accepted answer. It leaves the column in a Date format so that my 'order by' works correctly instead of just a varchar. – Milne Jun 21 '17 at 16:22
2

This does not work on SQL Server 2005 and earlier. The DATE data type was introduced with SQL 2008. – JerryOL Jan 24 '18 at 19:35
As stated above it is indeed nice to get a date data-type, but when a specific format is expected (like "dd MM yyyy") then more is needed. – trincot Dec 27 '18 at 18:49

score 15 · Answer 3 · answered Nov 20 '12 at 14:46

15

If you need the result in a date format you can use:

Select Convert(DateTime, Convert(VarChar, GetDate(), 101))

answered Nov 20 '12 at 14:46

Israel Margulies

8,656
2
30
26

score 11 · Answer 4 · answered Jan 17 '12 at 20:27

In addition to CAST and CONVERT, if you are using Sql Server 2008, you can convert to a date type (or use that type to start with), and then optionally convert again to a varchar:

declare @myDate date
set @myDate = getdate()
print cast(@myDate as varchar(10))

output:

2012-01-17

BIReportGuy · Answer 5 · 2018-06-28T21:34:06.233

7

If you have a datetime field that gives the results like this 2018-03-30 08:43:28.177

Proposed: and you want to change the datetime to date to appear like 2018-03-30

cast(YourDateField as Date)

edited Jun 28 '18 at 21:34

answered May 11 '18 at 14:28

BIReportGuy

799
3
13
36

1

you haven't added anything new, jabu.hlong already gave this answer 4 years ago... – Pawel Czapski May 11 '18 at 14:32
It is not assured that the date will be rendered in the OP's format. It might be yyyy-MM-dd, dd/MM/yyyy, dd/MM/yyyy 00:00:00, or still something else. – trincot Dec 27 '18 at 18:46

score 6 · Answer 6 · edited Mar 05 '15 at 19:27

6

With SQL Server 2005, I would use this:

select replace(convert(char(10),getdate(),102),'.',' ')

Results: 2015 03 05

edited Mar 05 '15 at 19:27

Konrad Krakowiak

12,285
11
58
45

answered Mar 05 '15 at 19:21

AGuest

61
1
1

score 5 · Answer 7 · answered May 26 '16 at 16:14

5

The shortest date format of mm/dd/yy can be obtained with:

Select Convert(varchar(8),getdate(),1)

answered May 26 '16 at 16:14

bsivel

2,821
5
25
32

score 0 · Answer 8 · answered Nov 14 '14 at 12:17

Just add date keyword. E.g. select date(orderdate),count(1) from orders where orderdate > '2014-10-01' group by date(orderdate);

orderdate is in date time. This query will show the orders for that date rather than datetime.

Date keyword applied on a datetime column will change it to short date.

score 0 · Answer 9 · answered Dec 17 '15 at 03:18

0

For any versions of SQL Server: dateadd(dd, datediff(dd, 0, getdate()), 0)

answered Dec 17 '15 at 03:18

Andrey

34
1

It is not assured that the date will be rendered in the OP's format. It might be yyyy-MM-dd, dd/MM/yyyy, dd/MM/yyyy 00:00:00, or still something else. – trincot Dec 27 '18 at 18:47

score 0 · Answer 10 · answered Dec 27 '18 at 17:13

0

The original DateTime field : [_Date_Time]

The converted to Shortdate : 'Short_Date'

CONVERT(date, [_Date_Time]) AS 'Short_Date'

answered Dec 27 '18 at 17:13

Yousef Albakoush

144
2
9

This returns a date data type which will be left to the interface to render as a string, so it may well produce "27/12/2018 00:00:00". That's not what the OP asked for. – trincot Dec 27 '18 at 17:56
This what he aimed to get -- dd/MM/yyyy ; and this what my answer produces 2018-11-20 – Yousef Albakoush Dec 27 '18 at 18:22
Well first of all 2018-11-20 is *not* dd/MM/yyyy but yyyy-MM-dd. But the rendering is really system (and locale) dependent. On my system it produces also hours, minutes and seconds (all zero). This is because you did not tell SQL how the date should be represented as string, and the OP needs an exact string format. – trincot Dec 27 '18 at 18:25
Firstly, 2018-11-20 is because my PC is configured for Arabic language use ; Secondly, i will try it after changing the short date in my PC – Yousef Albakoush Dec 27 '18 at 18:42

Convert a SQL Server datetime to a shorter date format

10 Answers10

Linked