SQL Server Date Format - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server Date Format

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2008-08-29 : 03:50:12

Hi there,

A few days ago we had a serious fault with our SQL Server cluster which resulted in us having to restore all our databases onto a new server. Everything is back to normal, it seems, with the exception of one thing.....

One of our applications will no longer save dates because they are being entered in the UK format (DD/MM/YYYY) and the fields in the database will only accept them in the American format of MM/DD/YYYY. The date format in the regional settings of the server is set to DD/MM/YYYY but this still does not appear to work.

Any ideas what I need to do to make the database accept dates in the UK format?

I should also note that this is a 3rd party application so I cannot really go and fiddle with the code....so a server side fix would be good! ;)

Thanks

Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 03:56:15

Have you tried Control Panel -> Regional Settings?

E 12°55'05.25"
N 56°04'39.16"

Gyto
Posting Yak Master

144 Posts

Posted - 2008-08-29 : 04:07:30

Yes, I did try that first of all but even after restarting the SQL services the problem still persisted.

I know it's a problem with the date format though because if you enter a date which is acceptable in both formats, such as 01/01/2008, then it will work fine....however if you enter a date such as 13/01/2008, which is only acceptable in the UK format, then it will not save it to the database.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 04:41:23

1 Always use proper DATETIME datatype to store dates
2 Express dates in YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

Gyto
Posting Yak Master

144 Posts

Posted - 2008-08-29 : 04:47:15

1) The data type is shortdatetime.
2) Unfortunately I did not develop the application, so I have no say over the design....but I would have to agree there....I just really need a quick fix for this database allowing it to accept dates in DD/MM/YYYY format as it used to before the change in servers....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 04:57:03

SET DATEFORMAT DMY
--DO PROCESS
SET DATEFORMAT MDY

Madhivanan

Failing to plan is Planning to fail

Gyto
Posting Yak Master

144 Posts

Posted - 2008-08-29 : 05:00:59

I've already tried that I'm afraid :(

How about using sp_configure to change the language of the server? Any thoughts?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 05:03:35

quote:
Originally posted by Gyto

I've already tried that I'm afraid :(

Did you get any error when you tried?

Madhivanan

Failing to plan is Planning to fail

Gyto
Posting Yak Master

144 Posts

Posted - 2008-08-29 : 05:08:43

No, the command completed successfully but it had no effect on the Application.

I've just found the following code to set the default for the server....


exec sp_addlanguage 'British', 'English',
   'January,February,March,April,May,June,July,August,September,October,
   November,December',
      'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
   'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday',
   dmy,1
   sp_configure 'default language', 1
   reconfigure with override

....only thing is I'm worried about it potentially affecting other apps....but if the setup of the old server was like this then I'm thinking it may not be a problem?!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources