Home » SQL & PL/SQL » SQL & PL/SQL » Date for Sunday prior to given date (Oracle )
Date for Sunday prior to given date Thu, 20 December 2018 12:14
 ssmith001 Messages: 37Registered: August 2018 Member
How do I calculate the date for the Sunday before a given date?

Example: Given Date = 12/20/18

Sunday Prior = 12/16/18
Re: Date for Sunday prior to given date [message #673990 is a reply to message #673989] Thu, 20 December 2018 12:20
 BlackSwan Messages: 26763Registered: January 2009 Location: SoCal Senior Member
ssmith001 wrote on Thu, 20 December 2018 10:14
How do I calculate the date for the Sunday before a given date?

Example: Given Date = 12/20/18

Sunday Prior = 12/16/18

Assume Day of Week (DOW) for Sunday is 1
Today (12/20/18) is DOW = 4
Prior Sunday = SYSDATE - DOW
Re: Date for Sunday prior to given date [message #673991 is a reply to message #673989] Thu, 20 December 2018 12:44
 Michel Cadot Messages: 67846Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```SQL> select next_day(to_date('12/20/18','MM/DD/YY'),'Sunday')-7 from dual;
NEXT_DAY(TO
-----------
16-DEC-2018```
Assuming you are English speaker.

Re: Date for Sunday prior to given date [message #673992 is a reply to message #673991] Thu, 20 December 2018 13:08
 ssmith001 Messages: 37Registered: August 2018 Member
Thank you Michel.
Re: Date for Sunday prior to given date [message #673993 is a reply to message #673992] Thu, 20 December 2018 13:56
 Solomon Yakobson Messages: 3055Registered: January 2010 Location: Connecticut, USA Senior Member
Michel's solution will give you Sunday before or equal to a given date, not before given date:

```SQL> select next_day(date '2018-12-23','Sunday')-7 from dual;

NEXT_DAY(
---------
23-DEC-18

SQL>
```

If you want Sunday before a given date and regardless of client NLS settings use

```trunc(given_date,'iw') - 1
```

For example:

```SQL> select trunc(date '2018-12-20','iw') - 1 from dual;

TRUNC(DAT
---------
16-DEC-18

SQL> select trunc(date '2018-12-23','iw') - 1 from dual;

TRUNC(DAT
---------
16-DEC-18

SQL>
```

SY.
 Previous Topic: date conversion Next Topic: Assign same Group id to rows
Goto Forum:

Current Time: Tue May 11 13:04:33 CDT 2021