Cast int to SmallDateTime

Question

I am trying to compare L.LoanDueDate (which is a smalldatetime) to the return value of ufnARCUGetLatestProcessDate() (which is an int) to see how many days the loan due date is past the last process date.

I receive an

Arithmetic overflow error converting expression to data type smalldatetime

error and can't seem to figure it out. Any help would be useful. Thank you in advance.

CAST(L.LoanDueDate - CAST(CAST((SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AS VARCHAR(10)) AS smalldatetime) AS INT)

I have also tried to use the following. CAST(L.LoanDueDate-CONVERT(smalldatetime,convert(char(8),(SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()))) AS INT) — JoBaxter, Feb 14 '13 at 23:50

score 0 · Answer 1 · answered Feb 14 '13 at 23:48

0

You can't compare an int to a smalldatetime. You need to compare two dates and for that you'd have to use datediff.

answered Feb 14 '13 at 23:48

DigCamara

5,540
4
36
47

Ricardo Souza · Answer 2 · 2013-02-15T00:13:15.530

0

You can cast the date column to int. It will give you the aproximate (rounded) date, without the time part, as an int.

Edit:

This will give you the date as a number for you to compare:

SELECT FLOOR(CAST(L.LoanDueDate as float)) -
(SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AS myValue
FROM ...

The key is to cast the date to number and not the number to date.

edited Feb 15 '13 at 00:13

answered Feb 14 '13 at 23:54

Ricardo Souza

16,030
6
37
69

That is why I am trying to cast it to a char first then getting the small date value from that. All i need is the date, no time. – JoBaxter Feb 14 '13 at 23:55

score 0 · Accepted Answer · answered Feb 15 '13 at 00:01

I was able to figure out the issue. DigCamare's comment on DATEDIFF reminded me that is what I needed to do even though you can cast a int to a smalldatetime so you can compare it.

DATEDIFF(d,L.LoanDueDate,CONVERT(smalldatetime,convert(varchar(10),(SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()))))

score 0 · Answer 4 · answered May 23 '13 at 15:05

0

Just wanted to add the following. This is what I am using to do a simple int to smalldatetime conversion. Hope it helps other people.

(SELECT CAST(CAST(ProcessDate AS VARCHAR(10)) AS SMALLDATETIME) FROM ufnARCUGetLatestProcessDate())

answered May 23 '13 at 15:05

JoBaxter

710
2
12
23

Cast int to SmallDateTime

4 Answers4