How to convert date to datetime?

Question

I have the following case, I’m trying to convert the one field date for datetime using the update

update tb_RHContratos
set DtCadastro = cast(DtCadastro as datetime)

But some of the date this does not format properly as are many record gets hard to identify which date is wrong.

I have the following msg error

Message 242, Level 16, Status 3, Line 13 The conversion of a type of date data in a type of datetime data resulted in a value outside the interval. The instruction has been completed

It has how to identify which date is out of range with a select or something?

Answer 1

Use the command ISDATE to verify what cannot be converted:

SELECT *
FROM tb_RHContratos
WHERE isdate(DtCadastro) = 0

The tip I was going to give was to use datetime2, that supports very old dates, but this goes against what you want, which is precisely locating what is in trouble.

---

EDIT

There’s one more function you can use: TRY_CAST.

SELECT DtCadastro, 
    CASE WHEN TRY_CAST(DtCadastro AS datetime) IS NOT NULL 
    THEN 'Ok'
    ELSE 'Falhou'
AS "Resultado da Conversão"
FROM tb_RHContratos