OraFAQ Forum: SQL & PL/SQL » how to get last week end date ?

Home » SQL & PL/SQL » SQL & PL/SQL » how to get last week end date ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get last week end date ? [message #603417]

Mon, 16 December 2013 01:33

malli123
Messages: 6
Registered: December 2013

Junior Member

Hi,
I need to pull the last week end date where my week stat day is 'monday' and END day is 'Sunday'. When i am running below query it is working fine for all days except for Monday. Please find the results. I am using TD 12.0.0.03 ver
select distinct date '2013-12-23'-dayofweek(date '2013-12-23'-2)-1 from fact;
ans : 12/15/2013
select distinct date '2013-12-24'-dayofweek(date '2013-12-24'-2)-1 from fact;
12/22/2013
select distinct date '2013-12-25'-dayofweek(date '2013-12-25'-2)-1 from fact;
12/22/2013
12/22/2013 is the last week end date .

Please help me to right a query which will works for all the days to go last week end.

Thanks in advance !

Report message to a moderator

Re: how to get last week end date ? [message #603420 is a reply to message #603417]

Mon, 16 December 2013 02:19

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For date '2013-12-22' the result should be?

Report message to a moderator

Re: how to get last week end date ? [message #603422 is a reply to message #603420]

Mon, 16 December 2013 02:27

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> break on end_prev skip 1 dup
SQL> with dates as (select sysdate+level-1 dt from dual connect by level <= 10)
  2  select to_char(dt,'Dy DD/MM/YYYY') cur_dt,
  3         to_char(dt-to_number(to_char(dt,'D'))+1,'Dy DD/MM/YYYY') end_prev
  4  from dates
  5  order by dt
  6  /
CUR_DT         END_PREV
-------------- --------------
Mon 16/12/2013 Sun 15/12/2013
Tue 17/12/2013 Sun 15/12/2013
Wed 18/12/2013 Sun 15/12/2013
Thu 19/12/2013 Sun 15/12/2013
Fri 20/12/2013 Sun 15/12/2013
Sat 21/12/2013 Sun 15/12/2013

Sun 22/12/2013 Sun 22/12/2013
Mon 23/12/2013 Sun 22/12/2013
Tue 24/12/2013 Sun 22/12/2013
Wed 25/12/2013 Sun 22/12/2013

Report message to a moderator

Re: how to get last week end date ? [message #603425 is a reply to message #603422]

Mon, 16 December 2013 02:40

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

How about this?

orclz> with dates as (select sysdate+level-1 dt from dual connect by level <= 10)
  2  select dt, trunc(dt,'iw')-1 from dates;

DT                  TRUNC(DT,'IW')-1
------------------- -------------------
2013-12-16 08:39:05 2013-12-15 00:00:00
2013-12-17 08:39:05 2013-12-15 00:00:00
2013-12-18 08:39:05 2013-12-15 00:00:00
2013-12-19 08:39:05 2013-12-15 00:00:00
2013-12-20 08:39:05 2013-12-15 00:00:00
2013-12-21 08:39:05 2013-12-15 00:00:00
2013-12-22 08:39:05 2013-12-15 00:00:00
2013-12-23 08:39:05 2013-12-22 00:00:00
2013-12-24 08:39:05 2013-12-22 00:00:00
2013-12-25 08:39:05 2013-12-22 00:00:00

10 rows selected.

orclz>

Report message to a moderator

Re: how to get last week end date ? [message #603426 is a reply to message #603422]

Mon, 16 December 2013 02:41

malli123
Messages: 6
Registered: December 2013

Junior Member

Michel Cadot

Thanks for helping

On 22/12/2013 last week end date is '12/15/2013', But your query gives me the 22/12/2013.

Hope below query is working for all the dates
'SELECT distinct (date '2013-12-22'-7)-dayofweek(date '2013-12-22'-1)+7 SU from fact;'

[Updated on: Mon, 16 December 2013 02:44]

Report message to a moderator