SQL, casting a string to date so I can use GETDATE()

I am using SQL Server Management Studio 18 against SQL Server 2016. I have some material that are in batches and those batches have expiration dates that are held as strings, but are basically in the format of ‘yearmonthday’, e.g. ‘20210312’ for March 3rd, 2021. My goal is to only see material that is expiring after the current date. Whenever I try to CAST the expiration date column AS DATE within the WHERE clause, I get this error:

Conversion failed when converting date and/or time from character string

(or something similar when trying different methods).

So, right now my code looks like this:

SELECT MaterialColumn, BatchColumn, CAST(ExpirationColumn AS DATE)
FROM StockTable
WHERE CAST(ExpirationColumn AS DATE) > CAST(GETDATE() AS DATE)

If I don’t do the WHERE clause, I know I can CAST the ExpirationColumn as DATE without issue, but when it’s in the WHERE clause, I run into that error. Is there any way I can filter to see only the dates that I want?

Answer

You can use try_cast() instead:

SELECT MaterialColumn, BatchColumn, CAST(ExpirationColumn AS DATE)
FROM StockTable
WHERE TRY_CAST(ExpirationColumn AS DATE) > CAST(GETDATE() AS DATE);

You can also find the bad values:

SELECT ExpirationColumn
FROM StockTable
WHERE TRY_CAST(ExpirationColumn AS DATE) IS NULL AND ExpirationColumn IS NOT NULL;

It sounds like you might need to fix some data values.