SQL Server Performance

Case Statement in DateAdd and Where Clause

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Jul 7, 2010.

  1. sqlderby Member

    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
  2. Adriaan New Member

    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.
  3. sqlderby Member

    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...
  4. Adriaan New Member

    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.
  5. Madhivanan Moderator

    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))

Share This Page