Results 1 to 4 of 4

Thread: NextDay

  1. #1
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Smile Unanswered: NextDay

    Hello everybody?

    Another issue has cropped up in my Oracle to SQL Svr migration. Can anybody suggest the equivalent code for the Oracle NEXT_DAY function?

    Here is a sample of how Oracle NEXT_DAY function works...

    SELECT NEXT_DAY(sysdate, 'Sunday') from dual ;

    Result:

    NEXT_DAY
    ---------
    12-DEC-04

    where sysdate is Oracle for GetDate() of SQL Svr. Forget about "from dual". So, what NEXT_DAY is doing is that it is returning the next coming date on which a Sunday will fall, if sysdate = 07 Dec 2004.

    Any help will be highly appreciated. Thx
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    You'll have to write this youself, as there is no immediate equivalent.
    You can pretty easily create a user-defined function that exactly mimics oracle NEXT_DAY()

    You would have to use the SQL function datename(weekday, date)
    and the SQL function dateadd(day, 1, date).
    Loop round a max of seven (six?) times using dateadd to add a day to your date, and then using datename to get the day description which you could then compare with your input parameter. return the appropraite date when a match is found.

    HTH, but I ain't coding it for you :-)

    Bill

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a non-looping algorithm:

    declare @TargetDay int
    set @TargetDay = 1 --Sunday
    select dateadd(d, (@TargetDay + 7-datepart(dw, getdate())) % 7, getdate())


    TargetDay must be supplied as an integer, according to the settings of your @@DATEFIRST property. Normally, Sunday = 1, Monday = 2, etc.

    Also, this returns 0 if the today is the TargetDay. Not sure exactly what you wanted in that case...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs up

    Thx a lot lindman and bill..
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

Posting Permissions

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