How to get a date in YYYY-MM-DD format from a TSQL datetime field?

Question

How do I retrieve a date from SQL Server in YYYY-MM-DD format? I need this to work with SQL Server 2000 and up. Is there a simple way to perform this in SQL Server or would it be easier to convert it programmatically after I retrieve the result set?

I've read the CAST and CONVERT on Microsoft Technet, but the format I want isn't listed and changing the date format isn't an option.

The BOL description for 126 is a bit confusion (never found an explanation for "T"). — nojetlag, Jul 23 '10 at 12:23
The "T" separates the date from the time. [See ISO 8601 on Wikipedia](http://en.wikipedia.org/wiki/ISO_8601) — krubo, Jun 05 '12 at 16:06

Darrel Miller · Accepted Answer · 2009-05-20T19:12:43.033

485

SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don't want.

edited May 20 '09 at 19:12

answered May 20 '09 at 19:05

Darrel Miller

139,164
32
194
243

3

No, but some clients have issues with the fixed length. – gbn May 20 '09 at 19:12
62

This post comes up in Google for converting to YYYYMMDD - so that one is: CONVERT(char(10), GetDate(),112) – NealWalters Jul 02 '14 at 14:26
1

This didn't work for me, suggestion above w/ a code 112 did. Thanks @NealWalters – AlexVPerl Jul 28 '14 at 05:12
5

The list of integer-codes for output styles: http://msdn.microsoft.com/en-us/library/ms187928.aspx – Ben Fransen Dec 31 '14 at 08:47
Code 126 is good for dates such as a date of birth in YYYY-mm-dd format: CONVERT(char(10), pat_dob , 126) as pat_dob – jjwdesign Oct 07 '15 at 21:14
Syntax for CONVERT: `CONVERT ( data_type [ ( length ) ] , expression [ , style ] )` then you want to use the style code for how you want the datetime to be formatted as per the [MSDN Docs](https://msdn.microsoft.com/en-us/library/ms187928.aspx) (make sure you look under date and time styles.) – chrissavage Jun 07 '16 at 16:06
sql2014 CONVERT(date, GETDATE(), 126) – chongo2002 Jun 08 '16 at 13:44
trimming of time component was what I was looking for. Thanks Mate! – Deepak Pathak Sep 01 '16 at 03:17
Arbitrary integer codes for formats? Seriously, Microsoft? I hope the guy that created this "API" is roasting in hell now. Or worse: Forced to use his own API! But thanks for pointing out, very helpful! – Patrick Bucher Dec 05 '17 at 12:38

score 166 · Answer 2 · edited Sep 08 '16 at 15:08

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

@MohsenNajafzadeh In this case Imran is only listing the date format string as a comment, so whether the month is capitalized is irrelevant; readers should know "month" is meant here rather than "minute" from the context of the question. — TylerH, Sep 23 '20 at 20:12

score 129 · Answer 3 · answered Dec 21 '15 at 09:53

129

Starting with SQL Server 2012 (original question is for 2000):

SELECT FORMAT(GetDate(), 'yyyy-MM-dd')

answered Dec 21 '15 at 09:53

Ignas Vyšnia

2,079
1
16
16

This is the sane flexible way to do date/time in recent SQL versions using Dotnet standard formats. Remember to capitalize month MM to distinguish from minutes. [All date time formats](https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings) – jim birch Nov 29 '18 at 22:16
FORMAT runs (usually) 43 times slower than CONVERT. I strong recommend that you never (and I don't use that word often) use FORMAT. – Jeff Moden Jun 23 '20 at 02:06

John Sansom · Answer 4 · 2009-05-20T19:08:38.580

36

The form you are after is listed in the books online documentation.

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

For example, try the following:

select convert(varchar,getDate(),120)
select convert(varchar(10),getDate(),120)

edited May 20 '09 at 19:08

answered May 20 '09 at 19:02

John Sansom

41,005
9
72
84

5

If you take the default, in this case, you get the time value - the entire format for format 120 is 'yyyy-mm-dd hh:mi:ss'. By explicitly declaring the length, it is trimmed to the format you specified in yout original note - 'yyyy-mm-dd'. – DaveE May 20 '09 at 19:14

Guffa · Answer 5 · 2014-05-10T22:02:51.397

27

The convert function with the format specifier 120 will give you the format "yyyy-MM-dd HH:mm:ss", so you just have to limit the length to 10 to get only the date part:

convert(varchar(10), theDate, 120)

However, formatting dates is generally better to do in the presentation layer rather than in the database or business layer. If you return the date formatted from the database, then the client code has to parse it to a date again if it needs to do any calculations on it.

Example in C#:

theDate.ToString("yyyy-MM-dd")

edited May 10 '14 at 22:02

answered May 20 '09 at 19:10

Guffa

687,336
108
737
1,005

1

Why the downvote? If you don't explain what it is that you think is wrong, it can't improve the answer. – Guffa May 10 '14 at 22:00

LosManos · Answer 6 · 2014-01-26T14:38:36.970

13

For YYYYMMDD try

select convert(varchar,getDate(),112)

I have only tested on SQLServer2008.

edited Jan 26 '14 at 14:38

answered Sep 07 '11 at 07:43

LosManos

7,195
6
56
107

score 10 · Answer 7 · answered Jan 12 '20 at 05:42

10

I'm not sure why the simplest way has been ignored/omitted in the answers above:

SELECT FORMAT(GetDate(),'yyyy-MM-dd');--= 2020-01-02

SELECT FORMAT(GetDate(),'dd MMM yyyy HH:mm:ss');-- = 02 Jan 2020 08:08:08

I prefer the second one because whichever language you speak, you will understand what date it is!

Also SQL Server always 'understands' it when you send that to your save procedure, regardless of which regional formats are set in the computers - I always use full year (yyyy), month name (MMM) and 24 hour format (capital HH) for hour in my programming.

answered Jan 12 '20 at 05:42

Hannington Mambo

998
2
13
28

It should continue to be ignored because it's a good 27 times slower than CONVERT. ;) – Jeff Moden Dec 17 '22 at 00:31
@JeffModen I need to update 2000 rows. Performance is not an issue in this situation. Answers that are less performant shouldn't be omitted purely because they are less performant. This is far more readable than using some magic int. – shinyshark Feb 19 '23 at 09:22
@ShinyShark - Heh.. you don't realize it but performance is always an issue because people will use such performance challenged answers for bigger things. – Jeff Moden Feb 19 '23 at 17:06
@JeffModen No they won't. Source: I didn't. Don't gatekeep simplicity for the sake of gaining some unrequired performance. The difference between 0.0001 and 0.0002 is 100%, but in the context of the situation it is negligible. – shinyshark May 06 '23 at 20:05
@shinyshark - Just because you didn't doesn't mean others won't. It's not "gatekeeping". It's called supporting the correct method to do something. – Jeff Moden May 08 '23 at 02:30
@shinyshark: Just a thought: shouldn't programming adapt to human nature? With all the advances made in memory, processing speed, AI, why shouldn't little things like these just be adapted to human "forms"? I think someone needs to go to work and re-look at this age-old date problem that has plagued humanity like forever! – Hannington Mambo Jul 10 '23 at 05:30

score 7 · Answer 8 · edited Sep 15 '16 at 12:14

7

For those who would want the time part as well (I did), the following snippet may help

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
                              --example -- 2008-10-02T10:52:47.513

edited Sep 15 '16 at 12:14

Miguel Ângelo Rodrigues

99
1
7

answered Jun 11 '13 at 07:30

user2431693

91
1
2

score 7 · Answer 9 · edited Aug 03 '12 at 16:52

7

One other way...

CONVERT(varchar, DATEPART(yyyy, @datetime)) + '/' + CONVERT(varchar, DATEPART(mm, @datetime)) + '/' + CONVERT(varchar, DATEPART(dd, @datetime))

edited Aug 03 '12 at 16:52

Brian Webster

30,033
48
152
225

answered Jan 05 '12 at 19:10

Athadu

71
1
1

1

This will create 1 digit dates as in 8/3/2012 if you want 2 digit mm/dd you need to left pad the dates. RIGHT('00' + CONVERT(varchar, DATEPART(yyyy, @datetime)), 2) for example – MindStalker Aug 19 '13 at 13:14

score 6 · Answer 10 · answered Oct 29 '12 at 09:26

6

replace(convert(varchar, getdate(), 111), '/','-')

Will also do trick without "chopping anything off".

answered Oct 29 '12 at 09:26

Rafael Emshoff

2,541
1
35
47

I prefer this version to avoid "year 9999" bugs associated with the convert(varchar(10)...) version. – Francis Huang May 28 '13 at 20:50

score 6 · Answer 11 · answered Dec 05 '13 at 13:33

6

In case someone wants to do it the other way around and finds this.

select convert(datetime, '12.09.2014', 104)

This converts a string in the German date format to a datetime object.

Why 104? See here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

answered Dec 05 '13 at 13:33

Krisztián Balla

19,223
13
68
84

score 5 · Answer 12 · edited Nov 13 '18 at 00:06

5

You may also use. This is by using the new datatype DATE. May not work in all previous versions, but greatly simplified to use in later version.

SELECT CAST(getdate() AS DATE)
SELECT LEFT(CAST(getdate() AS DATE), 7)

edited Nov 13 '18 at 00:06

Stephen Rauch

47,830
31
106
135

answered Nov 12 '18 at 23:46

Sams

63
1
4

score 5 · Answer 13 · answered May 20 '09 at 19:06

In your cast and convert link, use style 126 thus:

CONVERT (varchar(10), DTvalue, 126)

This truncates the time. Your requirement to have it in yyyy-mm-dd means it must be a string datatype and datetime.

Frankly though, I'd do it on the client unless you have good reasons not to.

score 4 · Answer 14 · edited Oct 13 '11 at 19:35

4

If you want to use it as a date instead of a varchar again afterwards, don't forget to convert it back:

select convert(datetime,CONVERT(char(10), GetDate(),126))

edited Oct 13 '11 at 19:35

Flexo

87,323
22
191
272

answered Oct 13 '11 at 16:00

Wayne Evans

157
13

wouldn't this convert it back to the system default format? – Ignas Vyšnia Dec 21 '15 at 09:45

score 3 · Answer 15 · edited Jul 03 '18 at 20:38

3

From SQL Server 2008 you can do this: CONVERT(date,getdate())

edited Jul 03 '18 at 20:38

Aaron Dietz

10,137
1
14
26

answered Jan 11 '18 at 01:05

Asher

348
1
3
19

score 2 · Answer 16 · edited Aug 30 '12 at 06:02

2

SELECT CONVERT(NVARCHAR(20), GETDATE(), 23)

edited Aug 30 '12 at 06:02

biegleux

13,179
11
45
52

answered May 20 '09 at 19:09

Dmitri Kouminov

593
2
6
12

1

why use NVARCHAR(20)? could there be special characters in there? – KM. May 20 '09 at 19:14
I prever to use UNICODE. Mos of my projects international ;-) – Dmitri Kouminov May 20 '09 at 19:16

score 1 · Answer 17 · answered May 20 '09 at 19:10

1

I would use:

CONVERT(char(10),GETDATE(),126)

answered May 20 '09 at 19:10

KM.

101,727
34
178
212

score 1 · Answer 18 · answered Mar 20 '12 at 09:26

SELECT Code,Description FROM TABLE

-- This will Include only date part of 14th March 2010. Any date with date companents will not be considered.
WHERE ID= 1 AND FromDate >= CONVERT(DATETIME, '2010-02-14', 126) AND ToDate <= DATEADD(dd, 1, CONVERT(DATETIME, '2010-03-14', 126))

-- This will Include the whole day of 14th March 2010
--WHERE ID= 1 AND FromDate >= CONVERT(DATETIME, '2010-02-14', 126) AND ToDate < DATEADD(dd, 1, CONVERT(DATETIME, '2010-03-14', 126))

score 0 · Answer 19 · edited Sep 08 '16 at 15:41

Using a CASE statement for each of the convert / cast functions always works for me:

Please replace tableXXXXY with your table name, and issueDate_dat with the name of your datetime field in that table:

SELECT  issueDate_dat, CONVERT(varchar, DATEPART(yyyy, issuedate_dat))  AS issueDateYYYY
, CASE WHEN (len(CONVERT(varchar, DATEPART(mm, issuedate_dat))) < 2) THEN '0' +CONVERT(varchar, DATEPART(mm, issuedate_dat)) ELSE CONVERT(varchar, DATEPART(mm, issuedate_dat)) END AS  issueDateMM
, CASE WHEN (len(CONVERT(varchar, DATEPART(dd, issuedate_dat))) <2) THEN '0' +CONVERT(varchar, DATEPART(dd, issuedate_dat)) ELSE CONVERT(varchar, DATEPART(dd, issuedate_dat)) END AS issueDateDD
FROM            tableXXXXY

Hope this was helpful. chagbert.

score 0 · Answer 20 · answered Feb 22 '17 at 21:35

0

This solution works for me, simple and effective (with 126 too)

CONVERT(NVARCHAR(MAX), CAST(GETDATE() as date), 120)

answered Feb 22 '17 at 21:35

anfehernandez94

45
9

score 0 · Answer 21 · answered Aug 28 '17 at 10:14

0

 IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);

cmdGetPaymentStatement.Parameters.AddWithValue("@pStartDate", DateTime.Parse("22/12/2017", culture, System.Globalization.DateTimeStyles.AssumeLocal)).IsNullable = true;

answered Aug 28 '17 at 10:14

Raj Kumar

61
6

Code above is used for providing a store procedure to parameter in the form of mm/dd/yyyy – Raj Kumar Aug 28 '17 at 10:16

score 0 · Answer 22 · answered Mar 23 '20 at 15:45

0

Seems unnecessary to do any strange things, if you want your date to be seperated by slash. Just escape it with a backslash. Otherwise you will end up with a dot.

SELECT FORMAT(GETDATE(),'yyyy\/MM');

Tested on SQL Server 2016

answered Mar 23 '20 at 15:45

cuilster

123
1
12

You should test it for performance. FORMAT is generally 43 times slower than even a complicated CONVERT. – Jeff Moden Jun 23 '20 at 02:41

CArnold · Answer 23 · 2017-02-24T13:42:19.233

If your source date format is all messed up, try something along the lines of:

select
convert(nvarchar(50),year(a.messedupDate))+'-'+
(case when len(convert(nvarchar(50),month(a.messedupDate)))=1 
    then '0'+ convert(nvarchar(50),month(a.messedupDate))+'-' 
    else convert(nvarchar(50),month(a.messedupDate)) end)+
(case when len(convert(nvarchar(50),day(a.messedupDate)))=1 
    then '0'+ convert(nvarchar(50),day(a.messedupDate))+'-'
    else convert(nvarchar(50),day(a.messedupDate)) end) 
from messytable a

score -1 · Answer 24 · answered Nov 04 '20 at 11:45

As string processing is expensive, and FORMAT more so, I am surprised that Asher/Aaron Dietz response is not higher, if not top; the question is seeking ISO 8601 date, and isn't specifically requesting it as a string type.

The most efficient method would be any of these (I've included the answer Asher/Aaron Dietz have already suggested for completeness):

All versions

select  cast(getdate() as date)
select  convert(date, getdate())

2008 and higher

select  convert(date, current_timestamp)

ANSI SQL equivalent 2008 and higher

select  cast(current_timestamp as date)

References:

https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but

https://en.wikipedia.org/wiki/ISO_8601

https://www.w3schools.com/sql/func_sqlserver_current_timestamp.asp

https://learn.microsoft.com/en-us/sql/t-sql/functions/current-timestamp-transact-sql?view=sql-server-ver15

Mahdi Yazdi Rad · Answer 25 · 2022-12-13T08:22:28.413

-2

change GetDate() to any format as String:

SELECT FORMAT(GetDate(), 'yyyy-MM-dd HH:mm:ss')

edited Dec 13 '22 at 08:22

answered Dec 13 '22 at 08:18

Mahdi Yazdi Rad

1
2

1

The question asks for a date, not a datetime string. There are *several* answers already that show how to convert to `date` or an ISO8601 datetime string – Panagiotis Kanavos Dec 13 '22 at 08:28

How to get a date in YYYY-MM-DD format from a TSQL datetime field?

25 Answers25

Linked

Related