Is this right and Is there a Better way to do

Hi

I need to accomplish something like this

If (entry date is greater than (current_cycle_dateX + 9)) or (withdrawal date is greater than 00/00/0000 and less than (current_cycle_dateY – 9))

Then

-else

End

Here

entry date and withdrawal dateare small date time

And

current_cycle_dateX and current_cycle_dateY are chardata type

I coded like this , is this right ,is there a simple way or better way to do this

case when
( ENTRY_DATE > (convert(datetime, SCL.S_Current_Cycle_S_Begin_GRDPER1)+ 9)) OR
(
(convert(varchar, WITHDRAWAL_DATE,101) > '00/00/0000')
and (convert(varchar, WITHDRAWAL_DATE,101) < (convert(datetime,SCL.S_Current_Cycle_S_Begin_GRDPER1)- 9))
)
then 'Y'
else ' ' end as field

Thanks

You should not use the convert(varchar, WITHDRAWAL_DATE, 101) to compare. This will compare the order of the strings, rather than the order of dates:

01/10/2007 would come before 12/01/1998 ("01" comes before "12")

when you're doing a date comparison, it is safest (IMHO) to compare Date datatypes.

Something like this. Watch out for NULL values...

Code Snippet

Case

When (

Entry_Date > Dateadd(d, 9, convert(smalldatetime,S_Current_Cycle_S_Begin_GRDPER1))

OR

(

WITHDRAWAL_DATE > 0

AND

WITHDRAWAL_DATE < Dateadd(d, 9, convert(smalldatetime,S_Current_Cycle_S_Begin_GRDPER1))

)

)

then 'Y'

else '' END as Field

sql