Came across a weird issue while working on a project recently. The process is an ETL job that imports data from CSV files into a table and then table is then used to create reports in PowerBI. The data has been fine for many years. As is the case with such issues it cropped up suddenly on a weekend with the below error.
Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The above issue is often confused with another more common error message below
Error converting data type varchar to datetime.
REFERENCES
However you can see that in conversion of varchar to datetime is usually caused when searching for something like 30th of Feb or 31st of Nov. The input text is an invalid date and therefore show the error.
The actual error message I got was a value out of range issue, this indicates that date is valid but there is a range of values against which the data is being validated. The answer becomes clear when we explore the date setting Two Digit Year cutoff in database properties. The SQL Sever “datetime” datatype being the original date column datatype uses 1753 as the start date cutoff. It’s basically the start year for the Gregorian Calendar and to ensure the dates are consistent the cut off starts from the year the calendar was implemented. This issue doesn’t exists in datetime2 or date and time datatypes which are modern and have ranges that accommodate ranges from ‘0001-01-01’ to ‘9999-12-31’
Lets look at the examples below
| Query | Result |
| declare @DateText varchar(20) = ‘9999-12-31’ select CAST(@DateText as date ) |  |
| declare @DateText varchar(20) = ‘0001-01-01’ select CAST(@DateText as date ) |  |
| declare @DateText varchar(20) = ‘0001-01-01’ select CAST(@DateText as datetime ) |  |
| declare @DateText varchar(20) = ‘0001-01-01’ select CAST(@DateText as datetime2 ) |  |
Naturally the solution is not the change the datetime to datetime2 since the data we got was invalid and fixing it requires fixing the data we got from the CSV not changing the datatype of the column or casting etc.
We could use TRY_PRASE as shown below to invalidate the rows that do not fit but in this case it would have resulted in incorrect counts when grouped by the date column in question so we have to actually fix the issue at the source.
