SQL DateTime Format

[DarkConsultant]

Hi All,

I do not know what the protocol is but I have posted this on two forums ...

I had to move a db from one server to another and several of the tables store DateTime data.

On the old server they appeared in Management Studio as dd/MM/yyyy (I will ignore the time part as this is not an issue). On the new server they appear as MM/dd/yyyy.

When this data is returned to the client I use CDate(<server data>).ToString("dd/MM/yyyy") to get the data. On the old entries I get good data but when I convert any new entries I get bad data (MM/dd/yyyy) so 12th of November becomes 11th of December.

Can the server be persuaded to return the data the same as the old entries or is there some way to detect what format I am getting?

TIA

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[gmmastros]

Check the language setting of login you use to connect to the database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DarkConsultant]

Hi,

It is set to British English.

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[gmmastros]

Please read this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

I would encourage you to make sure the default language for the server is set to British English, and then make sure that all the logins are set that way to.

You may need to manually clean up some data if it is stored in the database with the month and day reversed already.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DarkConsultant]

Hi Again,

I had already found the article and changed the language to english but this made no difference.

Yah I am cleaning it as I go along.

I have just installed a new table with a datetime column and manually inserted getdate() into it. The formatting and returns were perfect.

So I launched my app on both servers and stopped it before it sent an insert with a datetime column in it and both apps are sending in yyyy-mm-dd (ignoring time part) format.

The old server is perfect, the new server is badly formatted. If I try to insert a date that cannot be reversed like 13/10/2011 (cannot be 10/13/2011) the app reports an error.

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[gmmastros]

What do you get when you run this:

select Distinct Language from sys.syslogins

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

I'm no expert but could this also not be affected by the short date format setting located in the regional settings of the computer?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[DarkConsultant]

Hi BlueJay,

If only life was that simple ...

I have instigated a workaround which is working so I will put this to bed as unsolved ... unless anyone knows different!

Heartfelt thanks to all contributers.

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//