Please tell me what is error in my date comparison sql query

Question

Please help me to find out error in my SQL query. I have created this query to compare dates

select * from Joinplans jp
where cast(convert(varchar,GETDATE(),103) AS  datetime) BETWEEN    
    CASE(convert(varchar,jp.planstartDate,103) AS datetime) AND
    CASE(convert(varchar,DATEADD(DAY,jp.planDays,jp.planstartDate),103) AS DATETIME)

It's giving me the error:

incorrect near 'AS'

I am using SQL Server 2005.

Answer 1

You wrote case instead of cast in two instances.

Answer 2

If planStartDate is actually a date, then there is no need to cast it to a character column:

Select ...
From Joinplans jp 
where GetDate() Between planStartDate And DateAdd(day, jp.planDays, jp.planStartDate)

Now, if planStartDate is storing both date and time data, then you might want to use something like:

Select ...
From Joinplans jp 
Where planStartDate <= GetDate()  
    And GetDate() < DateAdd(day, jp.planDays + 1, jp.planStartDate)

This ensures that all times on the last date calculated via the DateAdd function are included

Answer 3

select * from Joinplans jp where CONVERT(VARCHAR(10),GETDATE(),23) BETWEEN
CONVERT(VARCHAR(10),jp.planstartDate,23) AND CONVERT(VARCHAR(10),DATEADD(DAY,jp.planDays,jp.planstartDate),23)