SQL Server 2005 datetime format

[medium_pimpin]

I'm trying to set the date format for a SQL Svr table to dd-mm-yyyy. I know how to query and convert the format, but is there a way to store the date format as dd-mm-yyyy? So if I just select * the date is correctly formatted?

Thanks.

[Emorex]

I'm trying to set the date format for a SQL Svr table to dd-mm-yyyy. I know how to query and convert the format, but is there a way to store the date format as dd-mm-yyyy? So if I just select * the date is correctly formatted?

Thanks.

Datetime is stored as per the server locale so either dd/mm/yyyy hh:mm:ss or mm/dd/yyyy hh:mm:ss. Only way to properly insert it formatted is to create a varchar(10) and insert it in there.

[Kami-]

Set the date/time formatting on the server to UK? ;)

[loople]

If that isn't an option at the start of query execution or database load you can use the command "set dateformat dmy" (not 100% confident on the syntax as I'm going from memory).

[BGM]

dates in sql server are such an enormous pain in the ass

execute this query, and let me know what you get back:

SELECT @@language, @@langid

it will probably say us english

so try this:

SET Language British

now newly inserted dates can be in the format dd-mm-yyyy (gb) and don't have to be mm-dd-yyyy (us)

Note: this wont fix already inserted data

if that doesn't work, try this:

EXEC sp_configure 'default language', 23 
RECONFIGURE

23 is uk english, let me know how you get on...

Tip: If you do your queries using dd-mmm-yyyy i.e. 4-oct-2008, they will always be correct

[+mrbester MVC]

Alternatively, ISO-style date formats should always work:

yyyymmdd[hhmm[ss]]

[neufuse Veteran]

A date/time in SQL Server is not stored in any date / time format, but the number of seconds since 1900 (SMALLDATETIME) or the number of milliseconds since 1753 (DATETIME)... what you see is just the display format which can be changed easily