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.

Solution 1:

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

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

Solution 2:

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

Solution 3:

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

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

Solution 4:

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)

Solution 5:

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