TSQL - Case Date Compare

Question

Hi,

I am trying to compare a date in SQL and create a new field to allow me to group the results by Todays date and other date(s). I am converting both dates to CHAR. However i am getting an expression error near = and ) - This is for an SSRS report.

There is already a fairly complex SQL statement and i am just trying to add this extra statement to help in grouping.

Here is an example.

Case WHEN Convert(Char(8), FieldDate, 103) = Convert(Char(8), GetDate(), 103) Then tmpDate = '1' ELSE tmpDate = '0' END

This results in an incorrect sntax near '=' This results in an incorrect sntax near ')'

Thanks

Answer 1

If your example is from your code (copy/paste), "Then tmpDate - '1'" won't work. You put a hyphen instead of an equal sign?

Answer 2

Why not compare the dates directly?

CASE WHEN DATEDIFF(day, FieldDate, GETDATE()) = 0 THEN 1 ELSE 0 END AS tmpDate

Answer 3

Move the field to the front:

select tmpDate = 
    CASE
    WHEN Convert(Char(8), FieldDate, 103) = Convert(Char(8), GetDate(), 103) Then  '1' 
    ELSE '0' END