CODEWITHSUNDEEP
sqlsql-serverdatetimetry-cast
Mark Dungey
Mark Dungey

Reputation: 13

try_cast and cast differing results. The conversion of a date data type to a datetime data type resulted in an out-of-range value

I have a two SQL 2017 databases.

Database 1 contains table(a) with a column of datatype "date"

Database 2 contains table(b) with a column of datatype "datetime"

I'm doing a basic insert statement from "table a" into "table b" using

SELECT CAST(si.InceptionDate AS datetime).

This is currently failing with the error

"The conversion of a date data type to a datetime data type resulted in an out-of-range value."

If I change the insert statement to

SELECT TRY_CAST(si.InceptionDate AS datetime) this now works. This makes sense in theory however the datetime column in "table b" doesn't contain any NULLs and isn't missing any records from "table a". How can try_cast manage to successfully change the datatype but cast can't?

I think this has something to do with the data being selected from "table a" because I created a dummy table with the "date" data type on database 2 and inserted into this first before inserting insert "table b" and this works no problem.

Can anyone think of a reason for this??? It has me stumped :(

Upvotes: 0

Views: 2974

Answers (2)

Thom A
Thom A

Reputation: 95903

There are 2 things that could be contributing here, and without any sample data, I can only guess which (though it could be both).

Firstly, let's address the 2 functions you have, TRY_CAST and CAST. TRY_CAST cannot return the error you have; if the conversion fails then NULL is returned. That is by design and the documentation will have told you that.

Next, you have 2 different data types, datetime and date. datetime is an older data type and is suseptical to problems based on the LOGIN's LANGUAGE setting. Specifically, for datetime (and smalldatetime) the format yyyy-MM-dd is not unambiguous. This can be reproed with the below:

SET LANGUAGE ENGLISH; --American

SELECT CAST('2021-01-13' AS datetime); --Works
GO

SET LANGUAGE BRITISH; --English; we speak ENGLISH in England...

SELECT CAST('2021-01-13' AS datetime); --Fails
GO

This doesn't happen with date:

SET LANGUAGE ENGLISH; --American

SELECT CAST('2021-01-13' AS date); --Works
GO

SET LANGUAGE BRITISH; --English; we speak ENGLISH in England...

SELECT CAST('2021-01-13' AS date); --Works
GO

Again, as we have no sample, then either 1 or both of these factors are contributing.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Presumably, your query is more complicated than you are showing.

My guess is that some of the values would result in conversion errors but these rows are filtered out of the final result. SQL Server has a habit of pushing expressions before filtering clauses. Once consequence is that errors on rows that would be filtered out cause the query to fail.

Upvotes: 2

Related Questions