select case in sql server for date filter

Hari Krishna

8y1.9k

Hi team,

I am working on sqlserver ,i want to filter the date condition in select case where condition

in the sql server stored procedure,

i tried in the below query but i am getting exception:

An unexpected token ">=" was found following "". Expected tokens may include: "ELSE WHEN END OVER CON

my query

Select * from OPENQUERY(DB2TEST ,'SELECT distinct tlf.* FROM TESTDB2 .TLEXP tlf

where tlf.VEH_SER_NO IS NOT NULL AND

( CASE WHEN tlf.OPN_STS_CD=''CAN'' THEN date(tlf.CANC_DATE ) >= date(''2018-03-18'')

else date(tlf.TS_ISS) >= date(''2018-03-18'')

end )

order by tlf.TS_ISS desc')

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sankara Krishnan Venugopal8y ago
Hi Hari,

Instead of OPENQUERY, you can use the EXEC to execute your select statement.
Query:

EXEC('SELECT distinct tlf.* FROM TESTDB2 .TLEXP tlf
where tlf.VEH_SER_NO IS NOT NULL AND
(CASE WHEN tlf.OPN_STS_CD=''CAN'' THEN tlf.CANC_DATE >= ''2018-03-18''
else tlf.TS_ISS >= ''2018-03-18''
end) order by tlf.TS_ISS desc')

The select Statement will be work. Please let me know, if anything.

Thanks.
Jayesh Agrawal8y ago
Please try this: Select * from OPENQUERY(DB2TEST ,'SELECT distinct tlf.* FROM TESTDB2 .TLEXP tlf where tlf.VEH_SER_NO IS NOT NULL AND (date(tlf.CANC_DATE ) >=CASE WHEN tlf.OPN_STS_CD=''CAN'' THEN date(''2018-03-18'') else date(tlf.CANC_DATE ) end AND date(tlf.TS_ISS) >= CASE WHEN tlf.OPN_STS_CD=''CAN'' THEN date(tlf.TS_ISS) else date(''2018-03-18'') end) order by tlf.TS_ISS desc')