CODEWITHSUNDEEP
sqlsql-serverdatet-sql
user15464793
user15464793

Reputation: 149

SQL Server Date Comparison issue

I am trying to flag a field in a different table whenever two dates do not have the right relationship. However, All dates in the dataset should not flag anything(should be 0 instead of 1), but they all flag. Could it be an issue with the date format changing during the query? Really stuck here

Example output

UPDATE Theatre_DQ_03_Integrity_CASES
SET AnaesToTheatreFlag = 1, TimeErrorFlag = 1
FROM Theatre_Cases_Landing a
INNER JOIN
(
    SELECT  Case_ID
    FROM Theatre_Cases_Landing 
    WHERE (CaseCancelled IS NULL or CaseCancelled = 'N') AND (CONVERT(datetime,Anaesthetic_Start_DateTime) > CONVERT(datetime,In_Theatre_DateTime)) 
    AND Anaesthetic_Start_DateTime IS NOT NULL AND In_Theatre_DateTime IS NOT NULL
) b
ON a.Case_ID = b.Case_ID

declare @var1 as  datetime2 = '2019-09-04 11:12:00.000'
declare @var2 as  datetime2 = '2019-09-04 11:13:00.000' 
if @var1 < @var2 PRINT('TRUE')

Upvotes: 0

Views: 118

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46320

You need to correlate rows to be updated with the rows from the other table. Otherwise, every row in the target will be updated.

I don't see a need to self-join Theatre_Cases_Landing. Below is an untested version with the correlation.

UPDATE target
SET AnaesToTheatreFlag = 1, TimeErrorFlag = 1
FROM Theatre_DQ_03_Integrity_CASES AS target
JOIN Theatre_Cases_Landing AS a ON a.Case_ID = target.Case_ID
WHERE
    (a.CaseCancelled IS NULL or a.CaseCancelled = 'N')
    AND CONVERT(datetime,a.Anaesthetic_Start_DateTime) > CONVERT(datetime,a.In_Theatre_DateTime) 
    AND a.Anaesthetic_Start_DateTime IS NOT NULL
    AND a.In_Theatre_DateTime IS NOT NULL;

Upvotes: 1

Related Questions