OraFAQ Forum: SQL & PL/SQL » Prior Week Saturday Date

Home » SQL & PL/SQL » SQL & PL/SQL » Prior Week Saturday Date (Oracle 10g)

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

Prior Week Saturday Date [message #618712]

Tue, 15 July 2014 00:43

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi,

My requirements is to get the date of saturday of prior week.

e.g.
Input:
Today: 15-Jul-2014 (i.e.Tuesday)
output:
12-Jul-2014 (i.e. Saturday)

I have used TRUNC with 'DAY' parameter as shown below:

Case 1: When run day is Sunday.
/forum/fa/12018/0/

Case 2: When run day is Thursday.
/forum/fa/12019/0/

Case 3: When run day is Saturday.
/forum/fa/12020/0/

Code Snippet for your test:

SELECT   TRUNC(TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY')) TODAY,
         TO_CHAR(TRUNC(TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY')),'DAY') TODAY_DAY,
         TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY') CURRENT_WEEK_START,
         TO_CHAR(TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY'), 'DAY')  CURRENT_WEEK_START_DAY,
         TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY') - 1 PRIOR_WEEK,
         TO_CHAR((TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY')-1), 'DAY') PRIOR_WEEK_DAY
  FROM   DUAL;

My Code for test:

TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY') - 1 PRIOR_WEEK,

I just want to know if this can have any exception.

Thanks in advance !!!

Report message to a moderator

Re: Prior Week Saturday Date [message #618714 is a reply to message #618712]

Tue, 15 July 2014 00:52

gauravgautam135
Messages: 33
Registered: December 2013

Member

Sorry Guys..... Those attachments is not working for me.

I will update it with code scenarios.

Report message to a moderator

Re: Prior Week Saturday Date [message #618716 is a reply to message #618714]

Tue, 15 July 2014 01:01

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi,

My requirements is to get the date of saturday of prior week.

e.g.
Input:
Today: 15-Jul-2014 (i.e.Tuesday)
output:
12-Jul-2014 (i.e. Saturday)

I have used TRUNC with 'DAY' parameter as shown below:

Case 1: When run day is Sunday.


SQL> SELECT   TRUNC(TO_DATE ('13-Jul-2014', 'DD-Mon-YYYY')) TODAY,
  2           TO_CHAR(TRUNC(TO_DATE ('13-Jul-2014', 'DD-Mon-YYYY')),'DAY') TODAY_DAY,
  3           TRUNC (TO_DATE ('13-Jul-2014', 'DD-Mon-YYYY'), 'DAY') CURRENT_WEEK_START,
  4           TO_CHAR(TRUNC (TO_DATE ('13-Jul-2014', 'DD-Mon-YYYY'), 'DAY'), 'DAY')  CURRENT_WEEK_ST
ART_DAY,
  5           TRUNC (TO_DATE ('13-Jul-2014', 'DD-Mon-YYYY'), 'DAY') - 1 PRIOR_WEEK,
  6           TO_CHAR((TRUNC (TO_DATE ('13-Jul-2014', 'DD-Mon-YYYY'), 'DAY')-1), 'DAY') PRIOR_WEEK_D
AY
  7    FROM   DUAL;

TODAY     TODAY_DAY CURRENT_W CURRENT_W PRIOR_WEE PRIOR_WEE
--------- --------- --------- --------- --------- ---------
13-JUL-14 SUNDAY    13-JUL-14 SUNDAY    12-JUL-14 SATURDAY

SQL> 
SQL>

Case 2: When run day is Thursday.

SQL> SELECT   TRUNC(TO_DATE ('17-Jul-2014', 'DD-Mon-YYYY')) TODAY,
  2           TO_CHAR(TRUNC(TO_DATE ('17-Jul-2014', 'DD-Mon-YYYY')),'DAY') TODAY_DAY,
  3           TRUNC (TO_DATE ('17-Jul-2014', 'DD-Mon-YYYY'), 'DAY') CURRENT_WEEK_START,
  4           TO_CHAR(TRUNC (TO_DATE ('17-Jul-2014', 'DD-Mon-YYYY'), 'DAY'), 'DAY')  CURRENT_WEEK_ST
ART_DAY,
  5           TRUNC (TO_DATE ('17-Jul-2014', 'DD-Mon-YYYY'), 'DAY') - 1 PRIOR_WEEK,
  6           TO_CHAR((TRUNC (TO_DATE ('17-Jul-2014', 'DD-Mon-YYYY'), 'DAY')-1), 'DAY') PRIOR_WEEK_D
AY
  7    FROM   DUAL;

TODAY     TODAY_DAY CURRENT_W CURRENT_W PRIOR_WEE PRIOR_WEE
--------- --------- --------- --------- --------- ---------
17-JUL-14 THURSDAY  13-JUL-14 SUNDAY    12-JUL-14 SATURDAY

SQL>

Case 3: When run day is Saturday.

SQL> SELECT   TRUNC(TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY')) TODAY,
  2           TO_CHAR(TRUNC(TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY')),'DAY') TODAY_DAY,
  3           TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY') CURRENT_WEEK_START,
  4           TO_CHAR(TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY'), 'DAY')  CURRENT_WEEK_ST
ART_DAY,
  5           TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY') - 1 PRIOR_WEEK,
  6           TO_CHAR((TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY')-1), 'DAY') PRIOR_WEEK_D
AY
  7    FROM   DUAL;

TODAY     TODAY_DAY CURRENT_W CURRENT_W PRIOR_WEE PRIOR_WEE
--------- --------- --------- --------- --------- ---------
19-JUL-14 SATURDAY  13-JUL-14 SUNDAY    12-JUL-14 SATURDAY

SQL>

My Code Snippet:

TRUNC (TO_DATE ('19-Jul-2014', 'DD-Mon-YYYY'), 'DAY') - 1 PRIOR_WEEK,

I just want to know if this can have any exception.

Thanks in advance !!!

Report message to a moderator

Re: Prior Week Saturday Date [message #618719 is a reply to message #618716]

Tue, 15 July 2014 01:53

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

Senior Member
Account Moderator

SQL> with
  2    data as (
  3      select to_date('13-Jul-2014','DD-Mon-YYYY') dt from dual
  4      union all
  5      select to_date('17-Jul-2014','DD-Mon-YYYY') dt from dual
  6      union all
  7      select to_date('19-Jul-2014','DD-Mon-YYYY') dt from dual
  8    )
  9  select dt,
 10         decode(to_char(dt,'D'), 'SAT', dt, next_day(dt-7,'SAT')) res
 11  from data
 12  /
DT          RES
----------- -----------
13-JUL-2014 12-JUL-2014
17-JUL-2014 12-JUL-2014
19-JUL-2014 19-JUL-2014

Report message to a moderator

Re: Prior Week Saturday Date [message #618729 is a reply to message #618719]

Tue, 15 July 2014 02:21

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi Michel,

Here 19-Jul-2014 is this week Saturday but i want it to be prior week.

No matter what day today is, it will always give date of prior week saturday.

e.g.
13-JUL-2014 should give 12-JUL-2014
17-JUL-2014 should give 12-JUL-2014
19-JUL-2014 should give 12-JUL-2014 instead of 19-JUL-2014.

Thanks,
Gaurav

Report message to a moderator

Re: Prior Week Saturday Date [message #618735 is a reply to message #618729]

Tue, 15 July 2014 02:34

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

Senior Member
Account Moderator

I thought you wanted this saturday in this case.

SQL> with
  2    data as (
  3      select to_date('13-Jul-2014','DD-Mon-YYYY') dt from dual
  4      union all
  5      select to_date('17-Jul-2014','DD-Mon-YYYY') dt from dual
  6      union all
  7      select to_date('19-Jul-2014','DD-Mon-YYYY') dt from dual
  8    )
  9  select dt,
 10         decode(to_char(dt,'Dy'), 'Sat', dt-7, next_day(dt-7,'SAT')) prev_sat
 11  from data
 12  /
DT          PREV_SAT
----------- -----------
13-JUL-2014 12-JUL-2014
17-JUL-2014 12-JUL-2014
19-JUL-2014 12-JUL-2014

Report message to a moderator

Re: Prior Week Saturday Date [message #618743 is a reply to message #618735]

Tue, 15 July 2014 02:57

gauravgautam135
Messages: 33
Registered: December 2013

Member

Thanks Michel !!!

Can u please check this one and let me know if there might be an exception to this solution.

SQL> with
  2      data as (
  3        select to_date('13-Jul-2014','DD-Mon-YYYY') dt from dual
  4        union all
  5        select to_date('17-Jul-2014','DD-Mon-YYYY') dt from dual
  6        union all
  7        select to_date('19-Jul-2014','DD-Mon-YYYY') dt from dual
  8      )
  9    select dt,TRUNC (dt, 'DAY') - 1 PRIOR_WEEK
 10    from data
 11  /

DT        PRIOR_WEE
--------- ---------
13-JUL-14 12-JUL-14
17-JUL-14 12-JUL-14
19-JUL-14 12-JUL-14

Report message to a moderator

Re: Prior Week Saturday Date [message #618746 is a reply to message #618743]

Tue, 15 July 2014 03:18

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

Senior Member
Account Moderator

TRUNC_DAY depends on the territory:

SQL> alter session set nls_territory=france;

Session altered.

SQL> with
  2    data as (
  3      select to_date('13-Jul-2014','DD-Mon-YYYY') dt from dual
  4      union all
  5      select to_date('17-Jul-2014','DD-Mon-YYYY') dt from dual
  6      union all
  7      select to_date('19-Jul-2014','DD-Mon-YYYY') dt from dual
  8    )
  9  select dt,TRUNC (dt, 'DAY') - 1 PRIOR_WEEK
 10  from data
 11  /
DT       PRIOR_WE
-------- --------
13/07/14 06/07/14
17/07/14 13/07/14
19/07/14 13/07/14

Report message to a moderator

Re: Prior Week Saturday Date [message #618748 is a reply to message #618746]

Tue, 15 July 2014 03:23

gauravgautam135
Messages: 33
Registered: December 2013

Member

Thanks Michel !!!

Report message to a moderator

Re: Prior Week Saturday Date [message #618757 is a reply to message #618748]

Tue, 15 July 2014 04:15

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

Senior Member
Account Moderator

Note that NEXT_DAY uses a day name (SAT for Saturday, for instance) and so depends on NLS_DATE_LANGUAGE parameter.
Unfortunately you cannot force it in the function as you can do it with TO_CHAR:

SQL> with
  2    data as (
  3      select to_date('13-Jul-2014','DD-Mon-YYYY') dt from dual
  4      union all
  5      select to_date('17-Jul-2014','DD-Mon-YYYY') dt from dual
  6      union all
  7      select to_date('19-Jul-2014','DD-Mon-YYYY') dt from dual
  8    )
  9  select dt,
 10         to_char(dt,'Day','nls_date_language=american') american_day,
 11         to_char(dt,'Day','nls_date_language=french') french_day
 12  from data
 13  /
DT       AMERICAN_ FRENCH_D
-------- --------- --------
13/07/14 Sunday    Dimanche
17/07/14 Thursday  Jeudi
19/07/14 Saturday  Samedi

Report message to a moderator

Re: Prior Week Saturday Date [message #618764 is a reply to message #618757]

Tue, 15 July 2014 05:10

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Michel Cadot wrote on Tue, 15 July 2014 11:15

Note that NEXT_DAY uses a day name (SAT for Saturday, for instance) and so depends on NLS_DATE_LANGUAGE parameter.
Unfortunately you cannot force it in the function as you can do it with TO_CHAR:

I had that problem once, that I needed some "language-independent" next_day.

So I created a package, that filled a numeric array (weekday 0-6) with the "local day names" from known dates from Sunday to Saturday, so I could call my function numeric_next_day without worrying about the NLS_DATE_LANGUAGE that was set:

SQL> CREATE OR REPLACE PACKAGE cal_functions
  2  AS
  3    FUNCTION numeric_next_day(in_date DATE, weekday_number NUMBER)
  4      RETURN date;
  5  END;
  6  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY cal_functions AS
  2
  3    TYPE day_array_type IS TABLE OF VARCHAR2(30) INDEX BY binary_integer;
  4
  5    day_array  day_array_type;
  6
  7    FUNCTION numeric_next_day(in_date DATE, weekday_number NUMBER)
  8      RETURN DATE
  9    IS
 10    BEGIN
 11      RETURN Next_Day(in_date,day_array(weekday_number));
 12    END;
 13
 14    PROCEDURE init
 15    IS
 16    BEGIN
 17      -- fill "day array" with local day names for next day
 18      day_array(0) := to_char(to_date('30-12-2012', 'dd-mm-yyyy'), 'Day');
 19      day_array(1) := to_char(to_date('31-12-2012', 'dd-mm-yyyy'), 'Day');
 20      day_array(2) := to_char(to_date('01-01-2013', 'dd-mm-yyyy'), 'Day');
 21      day_array(3) := to_char(to_date('02-01-2013', 'dd-mm-yyyy'), 'Day');
 22      day_array(4) := to_char(to_date('03-01-2013', 'dd-mm-yyyy'), 'Day');
 23      day_array(5) := to_char(to_date('04-01-2013', 'dd-mm-yyyy'), 'Day');
 24      day_array(6) := to_char(to_date('05-01-2013', 'dd-mm-yyyy'), 'Day');
 25    END;
 26
 27  BEGIN
 28    init;
 29  END;
 30  /

Package body created.

SQL>
SQL>
SQL> SELECT  Trunc(cal_functions.numeric_next_day(SYSDATE,3)) FROM dual;

TRUNC(CAL
---------
16-JUL-14

SQL>
SQL> SELECT  Trunc(cal_functions.numeric_next_day(SYSDATE,5)) FROM dual;

TRUNC(CAL
---------
18-JUL-14

SQL>

Report message to a moderator

Re: Prior Week Saturday Date [message #618777 is a reply to message #618712]

Tue, 15 July 2014 08:25

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Use IW format:

with data as (
              select to_date('13-Jul-2014','DD-Mon-YYYY') dt from dual union all
              select to_date('17-Jul-2014','DD-Mon-YYYY') dt from dual union all
              select to_date('19-Jul-2014','DD-Mon-YYYY') dt from dual
             )
select  dt,
        trunc(dt + 1,'iw') - 2 prior_saturday
  from  data
/

DT        PRIOR_SAT
--------- ---------
13-JUL-14 12-JUL-14
17-JUL-14 12-JUL-14
19-JUL-14 12-JUL-14

SQL>

SY.

Report message to a moderator

Re: Prior Week Saturday Date [message #618833 is a reply to message #618777]

Wed, 16 July 2014 00:48

gauravgautam135
Messages: 33
Registered: December 2013

Member

Thanks solomon !!!

Report message to a moderator

Previous Topic:	Query
Next Topic:	Query to get payroll values between dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 12:32:48 CDT 2024