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"

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)


"td" <(E-Mail Removed)> wrote in message
news:0C36D6A4-83BA-42FB-824B-(E-Mail Removed)...
> 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"

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

"Sylvain Lafontaine" wrote:

> 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)
>
>
> "td" <(E-Mail Removed)> wrote in message
> news:0C36D6A4-83BA-42FB-824B-(E-Mail Removed)...
> > 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"
>
>
>