开发者

Same SQL works on one machine but gives out-of-range error on another

开发者 https://www.devze.com 2023-03-14 21:51 出处:网络
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

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

开发者_如何学Go

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

0

精彩评论

暂无评论...
验证码 换一张
取 消