Why case statement fails while comparing data?

Question

I have stored OpeningDate as a varchar(100) and OpeningTime as Time in the table.

I have tried everything but this throws an error:

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

SELECT CONVERT(VARCHAR(100),CAST(OpeningDate AS DATE),106), --Cast to DATE first to get the style needed
       CASE WHEN 
            (   CAST(OpeningDate AS DATETIME)+ -- Cast to DATETIME
                CAST(OpeningTime AS DATETIME)  -- Also here
            ) < GETDATE() 
             THEN 1 
             ELSE 0 END AS OpeningVaild
FROM Works;

RegBes' answer is a good hint and you might read my comment below Chanukya's answer about culture dependant date/time formats... Always try to save your data in a column with the appropriate format. Using a string-format for a date value is slowing down your queries and is erronous - as you've found out... — Shnugo, Oct 15 '18 at 06:59

Shnugo · Answer 1 · 2018-10-15T08:06:19.943

Now one answer has got deleted - and with it my comment...

There are several issues:

CONVERT-style 106 is meaning dd mon yyyy. Is this your needed output format?
If you cast a value like 2018-04-02 to DATETIME this is culture dependant.
It is a bad idea to store a date-value in a string-typed column. This will slow down your queries and is erronous...

Try this out:

SET LANGUAGE ENGLISH;
SELECT CAST('2018-04-02' AS DATETIME)

SET LANGUAGE GERMAN;
SELECT CAST('2018-04-02' AS DATETIME)

Is it the second of April or the fourth of February?

Now try the same with a cast to DATE...

This is a bit quirky and weird, but it's legacy... Use the new formats DATE, TIME and DATETIME2 to avoid this.

Another sure approach is the ISO8601-format. This is yyyy-MM-ddTHH:mm:ss. You can cast a string like 2018-04-02T12:32:45 to DATETIME in any culture.

And of course there is the chance for invalid strings like 2017-02-29 (no 29th of Feburary in 2017!) or any kind of crap-data in your string-column...

You can search for crap-data with ISDATE (see RegBes'answer) or you might try TRY_CAST() (needs v2012+).

One more option is to simulate TRY_CAST with XML methods (since v2005):

DECLARE @SomeDates TABLE(d VARCHAR(100));
INSERT INTO @SomeDates VALUES('2018-04-02'),('2017-02-29'),('crap data')

SELECT TRY_CAST(d AS DATE)      --needs v2012 
      ,TRY_CONVERT(DATE,d,102)  --needs v2012 and allows to specify the mask (102: yyyy.mm.dd)
      ,(SELECT d FOR XML PATH('x'),TYPE)
       .value('(/x/d/text())[1] cast as xs:date?','datetime')
FROM @SomeDates;

score 1 · Answer 2 · answered Oct 15 '18 at 06:34

1

I suspect that some of the data in your OpeningDate field may not be a valid date format.

run this to get all the records that may have issues

select *
from works
where isdate(OpeningDate) = 0

answered Oct 15 '18 at 06:34

RegBes

554
3
11

check this out https://stackoverflow.com/questions/52736733/error-in-ssrs-expression-for-false-condition/52739800#52739800 – RegBes May 04 '22 at 12:18

Why case statement fails while comparing data?

2 Answers2