How to convert date to datetime?

Navigation
3

I have the following case, I'm trying to convert a date field to datetime using update 

update tb_RHContratos
set DtCadastro = cast(DtCadastro as datetime)

But some of the date does not format correctly as they are too many record is difficult to identify which date is wrong.

I have the following error msg

  

Message 242, Level 16, State 3, Line 13 The conversion of a type of   date data in a datetime data type resulted in a value outside of   interval. The statement has been terminated

Can you identify which date is out of range with a select or something?

    
asked by anonymous 06.11.2015 / 19:20

1 answer

5

Use the ISDATE command to verify what can not be converted: 

SELECT *
FROM tb_RHContratos
WHERE isdate(DtCadastro) = 0

The tip I was going to give was to use datetime2 , which supports very old dates, but this goes against what you want, which is precisely to locate 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
    
06.11.2015 / 19:49
In R, Using dplyr, create a new array GIT does not push