[sql-server] 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.

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

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)

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.

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

For YYYYMMDD try

select convert(varchar,getDate(),112)

I have only tested on SQLServer2008.

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

 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;

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

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

Will also do trick without "chopping anything off".

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

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)

I would use:

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

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

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

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

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

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

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

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.

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

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

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

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.

One other way...

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

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://docs.microsoft.com/en-us/sql/t-sql/functions/current-timestamp-transact-sql?view=sql-server-ver15