sql server express date format problems

[glyn6]

The boss has given me the code for an old system written in VB6 that connects to a SQL Express database.

When the code runs it flags up the error

Run-time error -2147217913 (80040e07)
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

When I debug the code the date it's trying to select is in the format yyyy-MM-dd
If I change the date format to dd/MM/yyyy, the code runs fine.

I think I've narrowed it down to a locale problem, rather than a code/connection problem.

I've checked all the Windows control panel and SQL Server property settings for locales and date formats and whatnot and apart from I'm on Windows 7, he's on XP, all the settings appear to be the same.

Anyone have any ideas of where a discrepency can occur or any other random ideas on why it doesn't work ? ;-)

Thanks

 

[gmmastros]

Setting a standard Date Format For SQL Server

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyn6]

Yeah, I've checked that stuff already, it all matches.

I've connected to the database on his machine and the program runs without a hitch, so it's definately something to do with the sql server or windows date format, rather than a good ol' fashioned coding bug

 

[gmmastros]

You checked the language setting of the user that is logging in to the database?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyn6]

I have now ;-) and they're the same too, which they should be really We took a backup of the database on his machine, copied the .BAK file onto mine and restored it

 

[gmmastros]

Can you run this and post the results?

Select Distinct Language From Master.sys.syslogins

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyn6]

Its "us_english" on both

 

[gmmastros]

If I change the date format to dd/MM/yyyy, the code runs fine.

Its "us_english" on both

us_english uses MM/dd/yyyy. I suggest you try changing the language for your login to 'British', disconnect from the database (close the app), log back in, and test again.

I suspect this will fix the problem. Please let me know either way.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyn6]

Tried switching the language for the logins and server to british english and that didn't work.

Tried using 11/11/201 as the date so it wouldn't matter about the month/day order, but that doesn't work :-S

All the hassle, the bosses doing it himself, so that's got me out of doing some work
Thanks anyway