Moved DB all dates are now US Format how to revert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Moved DB all dates are now US Format how to revert

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-05-19 : 07:27:51

Hey,

I restored my DB on a different server, and just noticed that all my dates have reverted to US Standard i.e 5/18/2010 12:00:00 AM

Id like to keep using 18/05/2010 how can I change this back ?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-19 : 07:32:57

When you say dates reverted to US format, do you mean it accepts date in US format at the time of insert/update? Or is it just a display issue?

Harsh Athalye
http://www.letsgeek.net/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-05-19 : 07:36:35

Ok I mean when I open a table which has a date column i.e Date Added

Locally it displays i.e 15/04/2010

I moved my DB to a different server via restore

Then I open the same DB and record and now the date is displayed as
4/15/2010

I would like to have my dates on the new database to work as before.

Hopefully this makes sense, thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-19 : 07:40:52

If this is only date display issue, you probably shouldn't worry because internally date is still stored the same as it was on other server. You can always format the date as per your choice at the front-end application or using SQL Server's CONVERT() function.

Harsh Athalye
http://www.letsgeek.net/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-05-19 : 07:43:12

Hmm yeah I guess its only a display issue but why has this happened ? So theres no way on a DB level even for display to change back to dd/mm/year settings

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-19 : 07:56:59

I believe default display format for date depends on which default locale and language information SQL server installed with. You can the default language using following command:

dbcc useroptions
GO

Harsh Athalye
http://www.letsgeek.net/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-05-19 : 09:06:31

when I run dbcc useroptions i see default language is english US alright but i cant seem to change the value. Where can I actually change it to UK just for this database I do not wish to affect the other databases

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources