Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    24

    Unanswered: Update a blank date column to be a year later and on the next sunday

    Hi,

    think this is a tricky one or now.. as it maybe

    i have similar to the following

    ACC_NO FROM_DATE TO_DATE
    1234 05/10/05


    i need to set the to date to be a year later and the next sunday
    eg one year on from the 05.10.05 would land on a thursday. so i need to set it tobe the sunday date which would be the 8th.

    but leave it alone if it is already a sunday?

    confusing !! hehe

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This will do it:

    next_day(add_months(from_date,12)-1,'SUN')

  3. #3
    Join Date
    Oct 2005
    Posts
    24
    im fresh to sql so dont hit me with a stick..

    can you explain how that works?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Working from the inside outwards:

    1) Move forward 12 months from a date:

    x := add_months(from_date,12)

    2) Go back one day:

    y := x-1

    3) Move forward to next Sunday:

    next_day(y,'SUN')

    Step 2 is required because the NEXT_DAY function always moves forward, even if the date argument is already a Sunday.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •