Question About Date Data

  • Hello,

    I have a situation in which I need to evaluate a table field called 'td' (in a CASE stmt I'm guessing), and change the day value in a field called 'ed' based on the 'td' value. 'ed' Is of type SmallDateTime.

    The CASE stmt might look something like this;

    "AlteredDate" =

    CASE When td = 'N' Then (subract 2 days from ed)

          ELSE When td = 'I' Then (subract 1 day from ed)

          ELSE When td = ' ' Then (do nothing to ed)

    END

    How would I set this up? I have started checking some things in BOL, but have not been able to put my finger on it yet.

    Any ideas?

    Thank you for your help!

    CSDunn

  • it would look something like this :

    Update MyTable set AlteredDate = case

    when td = 'N' then dateadd(d, -2, ed)

    when td = 'I' then dateadd(d, -1, ed)

    else ed

    end

  • There is no sense in updating a Field to its current value.

     

    Update MyTable set AlteredDate = case td

    when 'N' then dateadd(d, -2, ed)

    else dateadd(d, -1, ed) end

    where td IN ('I', 'N')

  • Doh... thanx for pointing it out :-).

  • There is if you update other fields along with the "cased" field.  Nulls will be put in the "cased" field.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks to all for your help!

    CSDunn

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply