How do I control the format in which MS-SQL Server returns datetime values?

Question

Specifically, I wish to get the date format in a pure (ISO) format:

YYYY-MM-DD HH:mm:ss

I'm looking for a SET command or something that I can use.

I do not wish to rely on the culture setting of the server.

Note: I'm interested in the string format in which dates are returned, not entered.

Answer 1

the formatting of the datetime depends on the client. You can use the convert function to display it as a string in the format of your choice.

Answer 2

To change the default format you need to add a new language (sp_addlanguage), set it's date format, then set the default language to it. More details can be found on this old technet article.

If you don't want to do that, then you can change it "per connection" using SET DATEFORMAT.

And if you don't want to do that, then you can use CONVERT to convert it to the relevent format string in each query.

Answer 3

Here's a fabby wee article I just googled up:

http://www.sqljunkies.ddj.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

What you're looking for is:

CONVERT(datetime,'05/08/2004',120)

This will return a date in the format you're after yyyy-mm-dd hh:mi:ss(24h) format (ODBC canonical, not ISO).

HTH
Kev