Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sql server express date format problems

Status
Not open for further replies.

glyn6

Programmer
Nov 2, 2009
561
GB
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
 
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 :p
 
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
 
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

 
Can you run this and post the results?

Code:
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top