Knowledgebase

Question

I get this error on my site when running with an MS SQL Server database:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Alternatively, I get problems with sessions or the front end seems to return blank pages.

Answer

These problems are to do with a mismatch between the date format used by CactuShop and the settings on the MS SQL Server database.

CactuShop forces all dates to US format to avoid ambiguity. However, your SQL Server might use different defaults if you're in Europe. There are a couple of ways you can try to change the default language of your database. Run the following against the database using Query Analyzer (MS SQL 2000) or as a 'New Query' if you use MS SQL 2005 Management Studio Express:

exec sp_configure 'default language', 1033
go
reconfigure
go

We've seen some cases where the database server doesn't recognize 1033 as the US English code, and instead prefers zero:

exec sp_configure 'default language', 0
go
reconfigure
go

If you get an error that the language is not recognized, try replacing the 1033 part with 0 (zero) which some SQL installations seem to recognize as US settings instead.

If this doesn't work, try the following:

EXEC sp_defaultlanguage 'yourusername', 'english'

Note that according to Microsoft, 'English' is the language spoken in the United States, while people in England speak 'British'. What a cheek eh?

Can't find the answer you need? Click here to open a support ticket.