Home » SQL & PL/SQL » SQL & PL/SQL » Date for Sunday prior to given date (Oracle )
Date for Sunday prior to given date [message #673989] Thu, 20 December 2018 12:14 Go to next message
ssmith001
Messages: 35
Registered: 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 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 67241
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
ssmith001
Messages: 35
Registered: 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 Go to previous message
Solomon Yakobson
Messages: 2969
Registered: 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 Jul 14 15:39:34 CDT 2020