Guys,

We are in the middle of migrating our SLX 7.5 data instance and we have come across a issue that has us stumped;

Previously working scripts are now resulting in error;

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This only presents itself when we are entering a date such as 17/02/1946; From the SQL trace it is running;

SELECT A1.CONTACTID, A1.MOBILE, A1.FIRSTNAME, A1.LASTNAME, A1.ACCOUNT, A1.USERFIELD9, A1.BIRTHDAY, (isNull(A2.CITY,'') + ', ' + isNull(A2.STATE,'') + ' ' + isNull(A2.POSTALCODE,'')) A2_CITYSTZIP, A1.WORKPHONE, A2.POSTALCODE A2_POSTALCODE, A1.EMAIL, A2.ADDRESS1 A2_ADDRESS1, A1.ACCOUNTMANAGERID, A1.WEBADDRESS, A1.WEBADDRESS2, (isNull(A1.LastName,'') + ', ' + isNull(A1.FirstName,'')) NAMELF16,A2.ADDRESSID FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) 
Where A1.BIRTHDAY = '1945-17-02' and A1.LASTNAME like 'R%'

This works fine on our existing setup 2005 server / SQL2008, but when it is run on our new instance Connection manager running on seperate box (2008) and SQL2008, it fails..

Anyone got any ideas???

Hi Matthew,

Does this work when entering a date such as 12/02/1946?

If so, it may be the region settings on the new box causing the issues. For example a UK date of 17/02/1946 will not work if querying US region as it will consider the 17 to be the month part (have encountered this myself several times).

Lee

Thanks

When you say the new box, are we talking the SQL box or the box with the SLX Connection Manager on?

We are sure that both are correctly set (UK) English.

Matt

Hi Matt,

I would check both to be sure but I would expect to be the SQL box. Are you able to test a date with a day value of 12 or less? If this doesn't work either then its probably not a region issue.

Lee