Update Statement with Case and DateDiff

Question

I feel confident this query is written correctly but I am continuing to have "Null" inserted into the expected column. My start and end date columns are datetime and contain date so I am unsure why this is not performing the datediff calculation and updating the column.

Am I doing something wrong that is easily overlooked?

DECLARE @X INT

UPDATE MyTable
SET @X = DATEDIFF(s, Start_Date, End_Date)
    ,Column1 = CASE 
        WHEN Start_Date <> NULL
            AND End_Date <> NULL
            THEN (
                    SELECT CONVERT(VARCHAR(10), (@x / 86400)) + ' Days ' + CONVERT(VARCHAR(10), ((@x % 86400) / 3600)) + ' Hours ' + CONVERT(VARCHAR(10), (((@x % 86400) % 3600) / 60)) + ' Minutes '
                    )
        ELSE NULL
        END

Sample Data: Start Date = 2018-08-08 00:00:00.000 End Date = 2020-08-08 00:00:00.000

(This is the actual data I am using in my real DB)

I should have added that I tried this already to the same result. I just swapped <> for IS NOT and I am still not seeing the calculation apply. — Classified Mystery, Apr 26 '19 at 22:13
Is that helpful? Added under code. It's the literal two dates I am working with to get this query to work. Both of these columns contain that data. So clearly, they are not null columns. So, that is why I am wondering why the calculation is not occurring. — Classified Mystery, Apr 26 '19 at 22:19

score 0 · Answer 1 · answered Apr 26 '19 at 22:08

Yes. Please see Not equal <> != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

forpas · Accepted Answer · 2019-04-26T22:53:59.870

0

You must drop the select statement inside case:

DECLARE @X INT

UPDATE MyTable
SET @X = DATEDIFF(s, Start_Date, End_Date)
    ,Column1 = CASE 
        WHEN Start_Date is not NULL AND End_Date is not NULL
            THEN    CONVERT(VARCHAR(10), (@x / 86400)) + ' Days ' + 
                    CONVERT(VARCHAR(10), ((@x % 86400) / 3600)) + ' Hours ' + 
                    CONVERT(VARCHAR(10), (((@x % 86400) % 3600) / 60)) + ' Minutes '
        ELSE NULL
    END

It seems that the subquery

(SELECT CONVERT....)

is evaluated before

@X = DATEDIFF(s, Start_Date, End_Date)

so @X is still null and the result of the subquery is null.

edited Apr 26 '19 at 22:53

answered Apr 26 '19 at 22:32

forpas

160,666
10
38
76

I sincerely appreciate you taking the time to explain this. This was the root cause. Everything is working well now. Do you have any documentation on why the subquery is processed first? – Classified Mystery Apr 26 '19 at 23:02
No I don't, it's just a guess, that's why I say: "It seems ....". I'm sure it's somewhere in the documentation but where?!! – forpas Apr 26 '19 at 23:05
I suspect the answer is [this](https://stackoverflow.com/a/2264085/92546). The following example shows the curious behavior: `declare @X as Int = 1, @Y as Int = 2, @Z as Int = 3; declare @Samples as Table ( Col Int ); insert into @Samples ( Col ) values ( 42 ); select @X as '@X', @Y as '@Y', @Z as '@Z', Col from @Samples; update @Samples set @X = @Y, @Y += 1, @Z = @Y; select @X as '@X', @Y as '@Y', @Z as '@Z', Col from @Samples; update @Samples set @X = @Y, @Y += 1, @Z = ( select @Y ); select @X as '@X', @Y as '@Y', @Z as '@Z', Col from @Samples;`. – HABO Apr 27 '19 at 03:02

Update Statement with Case and DateDiff

2 Answers2