SQL Date Format Day and Month Mixup

It seems this question is asked a lot, but none of the answers have given me results. I'm pulling my hair out here ... so hopefully someone has an answer.

I have a production server running SQL Server 2005. I backed up the db and restored it on my laptop's SQL Server Express instance. Now date queries are seriously affected. In the prod. server they are all stored as "4/13/2011 12:00:00 AM" format, but on my laptop they are showing as "2011-04-14 00:00:00.000". When I do a query trying to fi开发者_高级运维nd entries on "4/14/2011" my laptop gives me the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". Edit: This exact query runs fine on the production SQL server. (I'm using SSMS to run queries ... not an application/code)

I made sure my laptop's Windows regional settings are the same as the server (English(United States)) and everything on the Region and Language control panel is the exact same.

Finally I ran the following two queries:

select name ,alias, dateformat
  from syslanguages
     where langid =
      (select value from master..sysconfigures
         where comment = 'default language')

select @@language

Which gave the result of "*us_english, English, mdy*" ..... and "British" respectively. Where is this British coming from?! Now when I run this command before my query (in the management studio)

SET DATEFORMAT mdy

Then everything works perfectly! But in the syslanguages query it seems to already be mdy format. I'm not about to rewrite my application with "SET DATEFORMAT" all over the place - so hopefully someone has a clue. Maybe my SQL Express installation is buggared and I have to reinstall it?

I'm going to keep tinkering to hopefully get this to work.

It is the language settings of the login that you need to change.

You can do it through SSMS -> Security -> Logins -> YourLogin -> Properties -> Default Language

Or through TSQL

ALTER LOGIN [YourLogin] WITH  DEFAULT_LANGUAGE=[us_english]

If you specify things in dd-mmm-yyyy format, or better still, use DateTime parameters (rather than varchar input), you won't have a problem.

Try changing the default language to us-english, using the following:

EXEC sp_configure 'default language', 1033
RECONFIGURE

If that doesn't work, you could try language code 0.

Are you passing dates as string literals - or concatenating strings to build up the sql in your application?

If you are doing this, consider using parameters for the dateTime data types. Or at least escape them using the ODBC escape clause and format them as in { ts'yyyy-mm-ddhh:mm:ss[.fff] '} such as: { ts'1998-09-24 10:02:20' }.

It is most probably your application not passing the datetime properly.