Date Difference

[td]

The query
SELECT DATEDIFF(Day, [DateOut], [DateIn]) AS Days FROM Calibrations;
gives error "Overflow"

All dates entered are entered in dd/mm/yyyy format. The error can be fixed
by setting the default Logon locale to British English. I have tried the
CONVERT function as follows

SELECT DATEDIFF(Day, CONVERT(varchar(10), [DateOut], 120),
CONVERT(varchar(10), DateIn, 120)) AS Days FROM Calibrations;
gives error: "Conversion of char datatype to DateTime results in Out-Of-Range"

 

[Sylvain Lafontaine]

Probably because DateIn and DateOut are stored as a type string, not as
DateTime or ShortDateTime. If these fields are of type characters, setting
a format such as 120 will change absolutely nothing because you are
converting from a type string to another type string; ie., doing nothing
excerpt maybe truncating after 10 characters.

Use the command « set dateformat dmy » before computing your DateDiff or
change your convert function to convert toward a DateTime format instead of
converting toward the varchar(10) format.

 

[td]

Sometimes you miss the obvious. I have looked for this error for days but did
not check as I KNEW they were DateTime. But before replying to you that they
were I did a double check. Ooops!!!
Thanks

Probably because DateIn and DateOut are stored as a type string, not as
DateTime or ShortDateTime. If these fields are of type characters, setting
a format such as 120 will change absolutely nothing because you are
converting from a type string to another type string; ie., doing nothing
excerpt maybe truncating after 10 characters.

Use the command « set dateformat dmy » before computing your DateDiff or
change your convert function to convert toward a DateTime format instead of
converting toward the varchar(10) format.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

The query
SELECT DATEDIFF(Day, [DateOut], [DateIn]) AS Days FROM Calibrations;
gives error "Overflow"

All dates entered are entered in dd/mm/yyyy format. The error can be fixed
by setting the default Logon locale to British English. I have tried the
CONVERT function as follows

SELECT DATEDIFF(Day, CONVERT(varchar(10), [DateOut], 120),
CONVERT(varchar(10), DateIn, 120)) AS Days FROM Calibrations;
gives error: "Conversion of char datatype to DateTime results in
Out-Of-Range"