Reputation: 3970

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

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.

Upvotes: 318

Answers (25)

Mahdi Yazdi Rad

Reputation: 19

change GetDate() to any format as String:

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

Upvotes: -1

Adge Cutler

Reputation: 34

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

Upvotes: -1

cuilster

Reputation: 123

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

Upvotes: 0

Hannington Mambo

Reputation: 1090

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.

Upvotes: 12

Sams

Reputation: 93

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)

Upvotes: 8

Asher

Reputation: 367

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

Upvotes: 3

Raj Kumar

Reputation: 61

 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;

Upvotes: 0

CArnold

Reputation: 525

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

Upvotes: -1

anfehernandez94

Reputation: 45

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

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

Upvotes: 0

user2431693

Reputation: 91

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

Upvotes: 7

Chagbert

Reputation: 758

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.

Upvotes: 0

Imran

Reputation: 2089

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

Upvotes: 177

Ignas Vyšnia

Reputation: 2269

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

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

Upvotes: 138

Guffa

Reputation: 700182

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")

Upvotes: 28

LosManos

Reputation: 7692

For YYYYMMDD try

select convert(varchar,getDate(),112)

I have only tested on SQLServer2008.

Upvotes: 13

Krisztián Balla

Reputation: 20341

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

Upvotes: 6

Rafael Emshoff

Reputation: 2729

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

Will also do trick without "chopping anything off".

Upvotes: 5

Dmitri Kouminov

Reputation: 591

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

Upvotes: 2

Athadu

Reputation: 81

One other way...

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

Upvotes: 8

arun.passioniway

Reputation: 133

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))

Upvotes: 1

Wayne Evans

Reputation: 157

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))

Upvotes: 4

Darrel Miller

Reputation: 142014

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

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

Upvotes: 504

KM.

Reputation: 103579

I would use:

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

Upvotes: 1

John Sansom

Reputation: 41819

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)

Upvotes: 36

gbn

Reputation: 432200

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.

Upvotes: 5

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

Answers (25)

Related Questions