The conversion of a date data type to a smalldatetime data type resulted in an out-of-range value

SQL Server Administration
1

hi experts,
The view being selected from contains 1 date column which is defined as smalldatetime.
Selecting from the view view results in this error:

The conversion of a date data type to a smalldatetime data type resulted in an out-of-range value.
I ran
select MyDateColumn
from MyView
where MyDateColumn < '1900-01-01' or MyDateColumn > '2079-06-06'

0 rows found

How can I find the corrupted date value?
Thanks

2

Does the view definition do a CAST/CONVERT to smalldatetime? If so, why? Can you post the view definition here?

BTW, you'll want to run that query against the base table that contains that datetime column, not the view, in order to find the value.

1 Like
3

Thanks, @robert_volk

In the base table:
dteFreightBillRecd is datetime2(7)

Then in View 1:
CAST(dbo.View1.dteFreightBillRecd AS date)

Finally in View 2 (where the out-of-range error occurs):
CONVERT(smalldatetime, dbo.View1.dteFreightBillRecd)

4

I would check the data in that column, because a freight bill date that's 50+ years in the future sounds suspicious. Probably a typo.

If it is a legitimate value, then I would say to change the view definition, if you can, and remove that smalldatetime CONVERT.

5

The dates in the column appear to be valid: What do you make of that? I prefer to patch the corrupted date value rather than modify the view. This has been in place for years.
SELECT MIN([dteFreightBillRecd])
2000-01-01 00:00:00.0000000

SELECT MAX([dteFreightBillRecd])
2022-09-16 09:52:54.1870290

6

I'm not in front of a SQL Server at the moment, can you CONVERT the max and min values to a smalldatetime? They are definitely in range, I'm wondering if the time precision may be what's causing the error.

The other option is to use TRY_CONVERT in the view definition, that will return NULL instead of an error, and maybe help you pinpoint exactly which value is problematic.

7

Mysteriously, the date problem has been resolved. The failed job now completes successfully.
While this is good news, I'd like to have found the bad date. Hopefully it's a one-off that will not occur again.

Thanks for your ideas, Rob.