SQL – Convert String to Date and compare – Conversion failed when converting datetime from character string

I currently have dates stored in a general attribute field in the database as a string.

They are all stored in the format DD/MM/YYYY for example 01/01/2000

I am able to convert them them to datetime successfully by using the following in my select statement. For example CONVERT(DATETIME, attribute.field_value, 103) where attribute.field_value contains a date.

The SELECT statement works fine and returns the whole table with them correctly.

I can also return a column with todays date in the same format as follows CAST(getdate() AS datetime)

The problem occurs when I try to compare, now I only want to return everything that is newer than today in pseudo code that would dateNewerThanToday > dateToday

Therefore I have tried
WHERE CONVERT(DATETIME, attribute.field_value, 103) > CAST(getdate() AS datetime)

this gives me the error

Conversion failed when converting datetime from character string.

I have tried a multitude of cast/converts to get it to work. I have also wrapped by select so I am only doing it on dataset with the correct data.

Any help would be super useful! Many thanks in advance!!

Answer

A couple of things ..

1. You do not need to convert to GETDATE() to DATETIME data type as it already returns datetime data type.

2. Instead of CONVERT(DATETIME, attribute.field_value, 103)

   use

   CONVERT(DATETIME, attribute.field_value) or CAST(attribute.field_value AS DATETIME)

3. Add a where clause in your select to get only valid DATETIME values. something like

   WHERE ISDATE(attribute.field_value) = 1

   This will filter out any values which appears to be a date value but sql server doesnt see them as valid date values.

Important Not

Use appropriate data types. If this column is storing date values why not use the DATE or DATETIME data types.