sql server express - date format

Question

Hi,

In a sql server 2005 table I have a date column in dd/mm/yyyy format. But everything on sql server 2008 is yyyy-mm-dd.

Where abouts can I change the format the dates that are being stored in 2008 to that of 2005?

Thanks,

Answer 1

Datetime types are always physically stored in the same format. It uses 8 bytes, the first 4 bytes are an int that store the days before or after the base date of 1/1/1900, and the 2nd 4 bytes are an int that represent the number of 1/300 of a second since midnight. See here for more info - http://msdn.microsoft.com/en-us/library/ms187819%28SQL.90%29.aspx.

When you're viewing the data, you can change how the data is represented by using SET DATEFORMAT or by using the CONVERT function. Technet has a good list of the datetime functions for use with T-SQL as well.

Answer 2

SELECT CONVERT(DATETIME,'13/04/2006',103)

More about date/time formats here.

Answer 3

The default date format used when converting a DateTime to a string does not depend on the Sql version, it depends on the Collation of the Database.
You can change the Collation using Sql Management Studio, right click the Database, Properties, Options, Collation.
In your case if you set both databases to the same Collation you should see the same format.

EDIT
I just learned that what I wrote is not true. The default datetime format doesn't actually depends on the Default Language set for the user accessing the database. If this is a SQL user you can set it by right clicking the user, Properties, Default Language.
You can also set the Default Language on the database level, which is used when creating new users.

Answer 4

Here are some other good references on the crazy things people expect to do with datetime "strings":

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

And some great additional background on date / time types:

http://www.karaszi.com/SQLServer/info%5Fdatetime.asp