Why getting a conversion error when subtracting two dates?

Question

Post reply

Why getting a conversion error when subtracting two dates?

Henk Schreij

SSCarpal Tunnel

Points: 4554
More actions
June 28, 2004 at 4:19 am

#108414

I use a SQL that gives a error when two smalldatetime fields are subtracted:
"Arimethic overflow error converting expression to data type smalldatetime".
I only get this error when the date-part of the two date fields are equal.
The SQL part is
WHERE (DateDepart - DateTimeBooking > 14)
DateDepart has no time-part (time is 00:00)
DateTimeBooking has a time-part (eg. 11:37)
The Solution is:
use DATEDIFF(d, DateTimeBooking, DateDepart)
So my problem is solved, but can anyone explain to me why this error occurs?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 1

I found out that

WHERE (DateDepart > 14 + DateTimeBooking)

gives no error.

I think the problem is that the subtraction of two dates is seen as a date value and when the resulting value is negative

the date is negative.

And a negative date is not possible in MS SQL Server.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

Always use DATEDIFF to compare/add/subtract dates.

datetime and smalldatetime data types are not numbers, so SQL Server doesnt know what you are thinking when you say WHERE date - date > 14 (do you want it to tell you how many milliseconds, seconds, minutes, years are left over?). Remember there is more to a datetime than just days!

DATEDIFF tells sql server HOW you want to compare the dates. It can then compare the internal data types and return to you an int that is the difference between the dates, in the units you requested.

Take this code for example. The dates are the same for each call, but the return value is totally different depending on HOW you compare the dates:

PRINT DATEDIFF(yyyy,'1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(mm,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(dd,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(ww,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(hh,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(mi,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(ss,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')
PRINT DATEDIFF(ms,  '1 Jan 2004 13:34:44','2 Jan 2004 11:33:11am')

RESULTS
--------
0
0
1
0
22
1319
79107
79107000

Julian Kuiters
juliankuiters.id.au

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 3

Thank you Julian,

I'll never use date-subtract anymore, allways DATEDIFF.

But I think this is allowed:

When I want to show a date

eg the last date that a customer must have paid:

. DateDepart - 14 AS LastPayDay

Is this true

Or do I have to use DATEADD(d, -14, DateDepart)

as the subtract solution also can result in a error

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 4

DATEADD(d, -14, DateDepart) is preferable

DateDepart - 14 also works

and dates are integers

run the script to reporduce your error, after you've run the script the first time uncomment the last line (remove the -- )

declare @DateDepart as smalldatetime,

@DateTimeBooking as smalldatetime

set @DateDepart = getdate()

set @DateTimeBooking = (getdate() - 14)

select @DateDepart

select @DateTimeBooking

select (@DateTimeBooking - @DateDepart)

-- select (@DateTimeBooking - @DateDepart)

Max

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 5

Thanks I'll use DATEADD in the future.

(I corrected the 2nd line in a DateAdd SELECT)

Henk