Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RabidRick
Starting Member
4 Posts |
 Posted - 2009-01-08 : 02:00:57
|
| Hey there,I have a strange problem and it's sort of a general question. I can't post the actual source code but...Using DateDiff caused an overflow when comparing two datetime columns. This would make sense to me if the int value of the DateDiff (respective of the units) was over the max int size but it was in minutes and everything in the table in that column was in the same year (I checked, and I think for mi to overflow would need to be something like 4,080 years difference). Also there were no incorrectly The even stranger part was when I changed "mi" to "dd" it still gave the overflow error.The REALLY strange thing was the DateDiff statement itself was only throwing the error when compared to a value using the "greater than" operator. For example... DateDiff(mi, start_date, getdate() ) > 0 threw an overflow, but the same DateDiff(mi, start_date, getdate() ) < [whatever arbitrary number] did not error out. It seems this error would have to be caused by the DateDiff function itself overflowing the int buffer size (...I think) and I don't see how the comparison could have any impact at all.Another thing that might matter is when I got this error I had loaded a pretty large (~1.5 GB) database on an average PC (I think it's a core-2 duo with ~ 2 GB of RAM). I've only seen this happen on this specific box with the DB in question -- when I restored another DB for the app it was fine.Could this be due to the size of database or something I'm missing in the SQL Server settings? I know it seems like it must be in the data somewhere but I checked... unless it somehow got corruptIf anyone has seen this error when trying to datediff 2 dates that were absolutely not out of the int size limit for the unit used please let me know.the actual error: Difference of two datetime columns caused overflow at runtime |
|
|
RabidRick
Starting Member
4 Posts |
Posted - 2009-01-08 : 02:02:53
|
| *there were no incorrectly formatted dates -- only nulls which should be ok...sorry, couldn't find the edit button >.< |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-08 : 02:39:40
|
| are you using date values from two fields or two variables?Also are the two fields/variables od datetime datatype? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 03:06:43
|
Something else is wrong. I think you are mislead.The largest interval possible with INT and DATEDIFF and minutes, are theseSELECT DATEDIFF(MINUTE, '1753-01-01', '5836-01-25')SELECT DATEDIFF(MINUTE, '1900-01-01', '5983-01-24') As you can see, this is roughly a 4,000 year interval.Have you rebuilt the indexes on these columns?Please post full query, and we might spot the actual error.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
RabidRick
Starting Member
4 Posts |
Posted - 2009-01-08 : 08:05:24
|
| visakh16 - it's comparing getDate() to a varchar column formatted 'mm/dd/yyyy 12:30 PM'Peso - I think you're right, and no, I didn't rebuild the indexes... This is on a test environment and isn't happening on Production and I think something just went screwy... I just restored to another DB.I'm just lost as to the reason this could happen. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 08:13:47
|
VARCHAR? You are using VARCHAR to store dates? Why?Try thisSELECT DATEDIFF(MINUTE, CAST(start_date AS DATETIME), GETDATE())FROM Table1
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 08:15:15
|
Or this less forgiving suggestionSELECT DATEDIFF(MINUTE, CONVERT(DATETIME, start_date, 101), GETDATE())FROM Table1
E 12°55'05.63"N 56°04'39.26" |
|
|
|
RabidRick
Starting Member
4 Posts |
Posted - 2009-01-09 : 01:09:30
|
| Well, the varchar column is essentially changed to a datetime literal in the query... It should work properly as long as it's in a correct format SQL recognizes as a literal. That's why I checked that all the rows were formatted properly. |
|
|
|
|
|
|
|
|
DateDiff overflow under the int bound