Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: 23:59:59 of Last Sunday using Sysdate

    Hello

    I was wondering if some could help me achieve the end date of 23:59:59 of last Sunday's date

    The following code gives me last Sunday's date but as 05/01/2014 00:00:00

    trunc(next_day(sysdate-7, 'SUN'))

    I then found added a piece of code to the end to get 23:59:59, but now return the end day of Saturday instead of Sunday

    SELECT trunc(next_day(sysdate-7, 'SUN')) -(1/(24*60*60)) FROM DUAL

    2014-01-04 23:59:59

    I have been playing around with the syntex for about an hour now, an cannot get the desired results which would be 05/01/2014 23:59:59

    Can anyone advise on how I can change the syntex

    Kind Regards
    Helen

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that there might be (at least) two ways from your gotton results.

    (1)
    SELECT trunc(next_day(sysdate-7, 'SUN')) -(1/(24*60*60)) FROM DUAL

    2014-01-04 23:59:59

    I have been playing around with the syntex for about an hour now, an cannot get the desired results which would be 05/01/2014 23:59:59
    You got "2014-01-04 23:59:59" and you want "05/01/2014 23:59:59".
    Then, add 1 day to your result "2014-01-04 23:59:59".

    add 1 day to your result(i.e. "trunc(next_day(sysdate-7, 'SUN')) -(1/(24*60*60))" )
    might be
    SELECT trunc(next_day(sysdate-7, 'SUN')) -(1/(24*60*60)) + ((24*60*60)/(24*60*60)) FROM DUAL
    and, it is equivalent to
    SELECT trunc(next_day(sysdate-7, 'SUN')) + ( (24*60*60)/(24*60*60) -1/(24*60*60) ) FROM DUAL
    is equivalent to
    SELECT trunc(next_day(sysdate-7, 'SUN')) + ( (24*60*60 - 1) / (24*60*60) ) FROM DUAL

    Though, it might be not optimal,
    you would get surely your desired result.


    (2)
    The following code gives me last Sunday's date but as 05/01/2014 00:00:00

    trunc(next_day(sysdate-7, 'SUN'))

    I then found added a piece of code to the end to get 23:59:59, but now return the end day of Saturday instead of Sunday

    SELECT trunc(next_day(sysdate-7, 'SUN')) -(1/(24*60*60)) FROM DUAL

    2014-01-04 23:59:59
    How about this?
    SELECT trunc(next_day(sysdate-7, 'MON')) -(1/(24*60*60)) FROM DUAL

    Because, Monday is always just one day after Sunday.
    (returned "the end day of Saturday" by "trunc(next_day(sysdate-7, 'SUN'))" )
    Last edited by tonkuma; 01-09-14 at 08:54.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to remove this post, because of duplicated post.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is the same problem as what you posted here, and the solution I suggested there works just as well in Oracle.

    You're going to an awful lot of extra work to create inclusive ranges, when there is a much simpler solution available. Just calculate the date for Monday, and use a "less than" comparison instead of trying to split nanoseconds to find the last valid time on Sunday!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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