# Thread: 23:59:59 of Last Sunday using Sysdate

1. Registered User
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. Registered User
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".

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
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I want to remove this post, because of duplicated post.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579