How do I control the format in which MS-SQL Server

问题:

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.

回答1:

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.

回答2:

Here's a handy article I found:

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

回答3:

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.