Odd Date-Country problem for sa account

• Sam Shiell

  Ten Centuries

  Points: 1373

  More actions

  December 2, 2004 at 3:14 am

  #361699

  We have a very weird problem here.

  When dealing with dates (using CAST or CONVERT) the sa account translates them in the wrong Country format... We are in UK so our date formats are "dd-mm-yyyy" but whatever we do sa thinks we mean "mm-dd-yyyy".

  We've changed the sa default language but nothing is different. All other logins to this server work as expected.

  The odd thing about this was that it was OK then changed about the time some other software was installed on the server (some legecy software called Compaq Batch Server) which stores data on an SQL-Server database, but I can think of no reason that would do anything related to our problem.

  This is SQL-Server 7 with service pack 3. The host machine is NT4 with all the lastest hotfixes. The machines default locale is UK and keyboard layout is also UK-English.

  Anyone got any ideas on how to change the date translations for this account?

  Cheers

  Sam

   

   

• David.Poole

  SSC Guru

  Points: 76000

  More actions

  December 2, 2004 at 4:07 am

  #532156

  I don't have a SQL7 box to hand but in 2000 you do the following within Enterprise Mangler

  • Right click on the server and select properties
  • Choose the "Server settings" tab
  • Under default language for user select "British English"
  • Click OK

  The problem is that if you do this it may break your Compaq software.

  Dates are one of the things that SQL Server does very badly.

  I've resorted to coding my apps so that all date parameters are passed to stored procedures in a set string format corresponding to DD-MM-YYYY.

  Within my procedures I use SET DATEFORMAT DMY and then CAST my text parameter into either DATETIME or SMALLDATETIME.

  This means that my date routines will work on any configuration of SQL.

  Why SQL cannot have a standard YYMMDDHHMMSSmm entry for all dates I don't know

  LinkedIn Profile