0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
scorbin
  • 9
  • 5
  • Does this answer your question? [How to parse string into date?](https://stackoverflow.com/questions/10304373/how-to-parse-string-into-date) – Peter Smith Sep 13 '21 at 18:49
  • The Google search you might try is "tsql convert string to datetime" – Peter Smith Sep 13 '21 at 18:50
  • Use `TRY_CONVERT` which will return `NULL` if the conversion fails but won't fail the statement. It'll also let you see which rows are failing the conversion so you can put additional checks in. `SELECT * FROM StockTable WHERE TRY_CONVERT(DATE, ExpirationColumn) IS NULL;` In the case that you can't change the data source to use actual date types instead of strings. – squillman Sep 13 '21 at 18:52
  • 2
    *"I am on Microsoft SQL server studio 18"* SSMS is an IDE, it doesn't tell us anything about the version of SQL Server you are using (SSMS 18 supports SQL Server 2008-2019). *"expiration dates that are held as strings"* *this* is your real problem, the design. Fix the design, fix the problem. Stop storing dates as strings, that *aren't* strings. Very likely the reason your attempt fails is because you have bad data in your table, like `20211309`, `202111919` or perhaps `20210229`. – Thom A Sep 13 '21 at 18:53
  • @squillman Doing that doesn't seem to return any NULL values. All expiration dates seem to become '00000000'. – scorbin Sep 13 '21 at 19:14
  • @Larnu I agree with you on pretty much all points. I'm not sure if I will be able to get them to move to storing this data as the proper format, but there is most likely bad data involved. That is something we are trying to fix with better standardization of data input. Although, I'm not sure how I was able to CAST the same column as DATE outside of the WHERE clause if there is an issue with the any of rows. – scorbin Sep 13 '21 at 19:16
  • You *would* get the error still @scorbin . I *assume* you had a different `WHERE` so the query didn't error as the `SELECT` is processed after the `WHERE`. – Thom A Sep 13 '21 at 19:23

2 Answers2

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Honestly, if your dates are all stored in the format yyyyMMdd then there's no need to convert. Instead use a varchar parameter, as (at least) a varchar in the format yyyyMMdd had the same sort order as a date.

As a result you just convert GETDATE to the right format:

WHERE Expiration > CONVERT(varchar(8), GETDATE(), 112)

Of course, this doesn't change my statements in the comment; fix your design, don't stores dates as a string but as a date (and time) data type.

Thom A
  • 88,727
  • 11
  • 45
  • 75