DateTime formats in sql server 2005 and 2000

Hi there

I have an application running in two development environments, one using a sql server 2005 database and the other using a 2000 database. The application works on the 2000 database but when i try to insert values into the 2005 database the date format is incorrect (mm/dd/yyyy). I've checked the regional data settings on both machines and they are identical. The application (which i inherited) uses inline sql and when i dump the values before the sql command is run i get dd/mm/yyyy for the app running 2005 and mm/dd/yyyy for the app on 2000. I'm trying to determine if this is an issue with the machine itself and the .net framework installed or infact the two different versions on sql server.

thanks

hi jrogoz,

try inserting in the following format: yyyymmdd, it works no matter the local settings.

another option: try using DateTime object in the .net code and DATETIME datatype in SQLServer

hope this helps

|||

thanks for the reply

Could this be a machine setting somewhere? There are several instances where the inline sql contains this issue and i'd prefer to not have to change the code to get it to work just on this one machine...the way they have their production box setup it works fine as well.

thanks

|||

the format is language dependent

Try these with the user id you used to login to 2000 and 2005

select @.@.langid, @.@.language

select dateformat from master..syslanguages where langid = @.@.langid

Best option is to use the datatime object or specify the date in universal format YYYYMMDD

|||i did some more testing yesterday on the laptop in question and it seems that the issue is with the data and time setting of the laptop itself and not sql server. I simply wrote DateTime.now into a label and it displayed the date in the format dd/mm/yyyy where my machine displayed mm/dd/yyyy. I have matched up the regional settings as well and changed some registry settings to match what is on my computer and still no change. any ideas?|||

Hi,

I assume that you're concatenating strings to form a SQL command. So, the date/time info will be appended as the regional setting of the machine.

In this case, you can try to use parameters instead. Just assign date/time value as a DateTime object, and it is locale independent.