T-SQL to Return a Date as YYYY-MM-DD

Often while writing SQL queries I encounter situations where I want to display dates. In the past I would write applications that enforced dates to be in the mm/dd/yyyy format, but more often than not this led to confusion and mistakes  for the users of an application when working with the dates.

I am not sure why the mm/dd/yyyy format is still so popular since it is easily confused with the related dd/mm/yyyy syntax, and the mm/dd/yyyy is admittedly rather illogical in terms of order. Conversely some people prefer the dd/mm/yyyy syntax for dates, but I cannot agree with this either since it can easily be confused with the mm/dd/yyyy format.

In my opinion both the mm/dd/yyyy and the dd/mm/yyyy formats end up being incredibly confusing for users of an application. If one writes an application using dates in either format one will need to add descriptive labels reminding users of the format with each date field, and even then one cannot be certain the users have worked with the dates correctly.

The Bottom Line

Nowadays I like to enforce the very understandable date format of yyyy-mm-dd. I like this format since it is very clear to everyone and is supported by all major programming languages and database platforms. Also, if need be, this format is easily sort-able as a string which can be useful.

How to Return Dates in the YYYY-MM-DD Format

If you are writing a query to select a date from MS SQL Server and wish to only return the date component in the yyyy-mm-dd format, you can do so with a fairly straightforward syntax. Naturally if you are the DBA you can set up your database to return dates in the yyyy-mm-dd format, but otherwise it is always handy to know how to return date values in a desired format.

Apparently the yyyy-mm-dd format is part of an international standard termed ISO 8601 which also has Monday as the start of the week rather than Sunday. I’m not so convinced about the convenience of this, but I definitely like the straightforwardness and logic of the yyyy-mm-dd format.

Here is an example query that you can run in SSMS (SQL Server Management Studio) that returns the current date in the  yyyy-mm-dd format:

DECLARE @TestDate DATETIME
SET @TestDate = GETDATE()
SELECT Convert(VarChar(10),CAST(@TestDate AS DATETIME),126)

In this query the cast to the DateTime format within the SELECT statement is for safety reasons and is useful if you are not sure what will be passed in (ie: if you are receiving string-based input). This is not always necessary, and if you are certain of the data type of the variable (as in this case we are), then you can simply write your SELECT statement as:

SELECT Convert(VarChar(10), @TestDate, 126)

As you can see, it is the conversion of the date to a 10-character string format that truncates the returned value to display only the date component. The full format of the converted date is yyyy-mm-ddThh:mm:ss.mmm

If you are interested only in the time component in the format hh:mm:ss you can query this as follows:

SELECT Convert(VarChar(100), @TestDate, 108)

Also, if you are interested only in the date component without the dash as a separator you can use the following query to return the date in the format yyyymmdd:

SELECT Convert(VarChar(100), @TestDate, 112)

Just as a note, if you wish to retrieve the date in the format mm/dd/yyyy you can use the following:

SELECT Convert(VarChar(10), @TestDate, 101)

Conclusion

For many years I preferred to use the mm/dd/yyyy syntax, but almost inevitably this format causes confusion and mistakes. To clear any ambiguity surrounding dates I now always use the yyyy-mm-dd format, which is working very well.

I hope the information in this article is as useful to you as it has been for me.