Why does my code execute IF block even if dates comparison fail?

Question

Expiry Date = '2017-10-16' and ExpiryTime='12:00pm' in table and in our country Getdate is '2017-10-16' and currentdatetime is '2017-10-16 11:05:33.503'

but still, my code executes the IF condition which it should not. Why ?

        Declare @ExpiryDate date
        Declare @ExpiryTime varchar(10)

        Set @ExpiryDate= (Select convert(varchar(11), ExpiryDate, 106) from Works where NIT_No= @NITNo and WorkNo= @WorkNo)
        Set @ExpiryTime= (Select CAST(ExpiryTime as TIME(0)) from Works where NIT_No= @NITNo and WorkNo= @WorkNo)

        IF(CONVERT(DATETIME,CONVERT(VARCHAR,@ExpiryDate,106)+ ' ' + @ExpiryTime) <= CONVERT(datetime, GETDATE()))   
        Begin
            RAISERROR('Sorry, Expiry date and time has passed', 16, 10);
            return;
        End

Why are you using conversion code 106 and also why are doing so many conversions between varchar and date? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 16, 2017 at 6:11
I'm confused about 12:00pm...I can't tell if that's 2017-10-16 morning (0:00) or 2017-10-17 0:00...in the first case, your IF is true. — René Vogt
– René Vogt, Commented Oct 16, 2017 at 6:14

Raphael Müllner · Accepted Answer · 2017-10-16 06:20:34Z

2

12:00pm is translated to 00:00 in 24 hour format. If you combine the current date and 12:00pm, you expect the result to be midnight of the next day, but actually you get midnight of the current day.

That should work:

    Declare @ExpiryDate date
    Declare @ExpiryTime varchar(10)

    Set @ExpiryDate= (Select convert(varchar(11), ExpiryDate, 106) from Works where NIT_No= @NITNo and WorkNo= @WorkNo)
    Set @ExpiryTime= (Select CAST(ExpiryTime as TIME(0)) from Works where NIT_No= @NITNo and WorkNo= @WorkNo)

    declare @dateTimeCombined datetime = dateadd(ms, datediff(ms, '00:00:00', @ExpiryTime), cast(@ExpiryDate as datetime))

    IF @dateTimeCombined <= CONVERT(datetime, GETDATE())
    Begin
        RAISERROR('Sorry, Expiry date and time has passed', 16, 10);
        return;
    End

answered Oct 16, 2017 at 6:20

Raphael Müllner

4383 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does my code execute IF block even if dates comparison fail?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related