CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Gayu
Gayu

Reputation: 487

how to compare 2 dates with different formats in sql server 2012

I have 2 dates like the following.

End_Date = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '01/01/2000')
Cur_Date = SELECT CONVERT(VARCHAR(10),GETDATE(),111

I want to check if End_Date is less than Cur_Date so that I could do a particular update.

My current query is like the following

update bed
set Status_Avai_Occ = 
    case when (
                ( select  End_Date 
                  from Patient_Record 
                  where b_ID= @b and
                  End_Date <> '-' 
                  and End_Date is not null
                ) <  (
                       SELECT CONVERT(VARCHAR(10),GETDATE(),111)
                     )
               ) then 'Available'

but I am getting the following error:

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 2457

Answers (2)

Tim
Tim

Reputation: 8921

You can round getdate() to midnight so:

         SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Upvotes: 0

Young Bob
Young Bob

Reputation: 743

The only line I can see which would give that error is

End_Date <> '-'

which seems to imply that End_Date is a DATE or DATETIME column. If so you can simple compare dates using:

End_Date < GETDATE()

Upvotes: 1

Related Questions