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 programatically 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.

Answer 1

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)

Answer 2

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

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

Answer 3

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.

Answer 4

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

Answer 5

I would use:

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

Answer 6

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.

Example in C#:

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

Answer 7

The BOL description for 126 is a bit confusion (never found an explanation for "T").