The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
When I run the SQL query on my desktop machine, its fine. No errors or anything. However, when I execute the same code on the server that will be running it, it fails.
I've determined this is the section of SQL causing the issue. DateOfRun is a DateTime field.
,(SELECT intField
FROM tableA
WHERE RowIdentifier= ParentTable.RowIdentifier
AND DateOfRun = Convert(Varchar(10),ParentTable.OfferOutcomeDateTime,120)) AS Days
Why would this work on one machine, but not the other?
That's definitively odd and likely to do with regional settings but its like the old joke
A man goes to a doctor's office. He says, "Doctor, it hurts when I raise my arm over my head." The doctor replies, "Then don't raise your arm over your head."
So don't do that. Do this instead
WHERE RowIdentifier= ParentTable.RowIdentifier
AND DateOfRun =
DATEADD(DAY, DATEDIFF(DAY, 0, ParentTable.OfferOutcomeDateTime), 0)
Its the best way to go see Thomas' answer to Most efficient way in SQL Server to get date from date+time?
Then it will work regardless of regional settings because it never gets represented as a string
My guess would be that DateTime conversion from string fails because of different cultures on the local and server environments.
You don't mention your flavour of SQL, but the most likely candidate is a discrepancy between the input format of the char field and the system's locale settings.
"3/30/2011" is the 30th of March in the US, but makes no sense in the UK. "30/3/2011" is the 30th of March in the UK, but makes no sense in the US.
This would work on one machine and not the other if the data is different on the different machines.
It would also not work if your regional settings are different.
You should do something like
SET DATEFORMAT dmy
to set the format you want to use.
You could also convert/cast your date to local settings before you convert it to a varchar - http://msdn.microsoft.com/en-us/library/ms187928.aspx
精彩评论