Is there a way to get a week old data from a table were the date field is a varchar - SQL Server 2016

Question

I'm getting an error

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

when I try to get a week old data from a table in SQL Server 2016.

The date column in my table has a datatype of varchar. So in my query, I try to convert it to datetime first and get the 1 week back result.

SELECT
    USERNAME, UDATE 
FROM
    SAP_CDHDR 
WHERE
    CAST(UDATE AS DATETIME) >= DATEADD(day, -7, CAST(GETDATE() AS DATE))

Sample input:

USERNAME     UDATE
--------------------------
User 1       26.07.2017
User 2       02.05.2017
User 3       08.12.2017
User 4       15.07.2016
User 5       20.10.2016

Answer 1

Try using convert() with a format specifier:

select  USERNAME, UDATE
from  SAP_CDHDR
where convert(date, udate, 104) >= DATEADD(day, -7, cast(GETDATE() as date))

Here is a db<>fiddle showing that it works for your sample data.

If you have bad date values in the data, use try_convert().

Also, fix the data so your values are stored using the correct type!

EDIT:

To find the bad date values, use:

select  USERNAME, UDATE
from  SAP_CDHDR
where try_convert(date, udate, 104) is null