Hi All I need to add case condition in DateAdd and where clause as follows... Pls help me in this... Current One... Select dateadd(ss,-loggedOntimeToHalf,dateadd(mi,30,DateTime_IST)) as 'LogOutDateTime', Require like this but not working... Select dateadd(ss,-loggedOntimeToHalf,dateadd(mi,30,(case when @locationId=5 then DateTime_PHT else DateTime_IST) end )) as 'LogOutDateTime', Second same condition in Where Clause... Current One... Where DateTime_PHT >=DateAdd(dd,-(2),@dForDate) AND DateTime_PHT <=DateAdd(dd, (2),@dForDate) Require like this but not working... where (1=(case when @LocationID = 5 then (DateTime_PHT >=DateAdd(dd,-(2),@dForDate) AND DateTime_PHT <=DateAdd(dd, (2),@dForDate)) else (DateTime_IST >=DateAdd(dd,-(2),@dForDate) AND DateTime_IST <=DateAdd(dd, (2),@dForDate)) end)) Thanks
You need to get your brackets straight: END is outside of the bracketing that starts at CASE. Hm - not sure if the -loggedOntimeToHalf bit would work - is that a column, which you're multiplying by -1? If so, please note that this kind of shorthand is bad news for anyone else who may need to look at this code in future.
The Current code is working fine....I need help on required ones.... -loggedOntimeToHalf is columns from table... is it bad news ? pls let me know how would write in correct manner? Thanks...
There's a starting bracket before CASE, so the corresponding closing bracket must be after END. You put the closing bracket before END, that's a syntax error. The bad news is the minus sign: it's easy to overlook, so if someone else ever needs to understand what is going on, it will be easier if you write: (owner.tablename.loggedOntimeToHalf * -1) The addition of owner and table name (or an alias) is more than just a readability issue - it can actually help performance since it supports SQL Server's chances of finding a reusable query plan.
WHERE (@location=5 and DateTime_PHT >=DateAdd(dd,-(2),@dForDate) AND DateTime_PHT <=DateAdd(dd, (2),@dForDate)) OR ((@location<>5 and DateTime_IST >=DateAdd(dd,-(2),@dForDate) AND DateTime_IST <=DateAdd(dd, (2),@dForDate))