Convert NVARCHAR DATE to DATETIME using CHARINDEX

Question

I am getting the following error when trying to convert nvarchar column to datetime.

Select *
from table 1
where
convert(datetime, col1) >= '2018-08-29 00:00:00.000'

Error message

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

Col1 is an NVARCHAR column (I have no control over this data type so I cannot change it)

I have seen some examples of charindex but can't seem to get it to work, any help getting the code right for it to work is much appreciated.

UPDATE - '2018-08-29 00:00:00.000' is a datetime column which needs to stay that was in order to do the where clause.

Answer 1

Use try_convert():

Select *
from table 1
where try_convert(datetime, col1) >= '2018-08-29'  -- time is not necessary

To find the values that are causing a problem:

select col1
from t
where try_convert(datetime, col1) is null and col1 is not null;

Note: You may need a format argument for the conversion. In the end, though, you should fix your data to use the correct type for col1.

Answer 2

You can define SET DATEFORMAT YMD before the query start. have a look of below query,

SET DATEFORMAT YMD
Select *
from table 1
where
convert(datetime, col1) >= '2018-08-29 00:00:00.000'