CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
aadi
aadi

Reputation: 27

Previous Weekdays

I have a requirement in which i have to find start and end date. Start date is First sat of the previous month of created date and end date is previous friday of created date. Eg Below .. I am passing created date and need to derive start and end date like this below.

CREATED_DT            Start_date        end_date
04/08/2015 15:36      04/07/2015 00:00  31/07/2015 23:59
07/07/2015 15:32      06/06/2015 00:00  03/07/2015 23:59

Upvotes: 0

Views: 192

Answers (4)

aadi
aadi

Reputation: 27

I have achieved by this way

end date: Where created _dt is date value what i am passing..!! 

 next_day(TRUNC(to_date(created_dt,'DD-MM-YYYY HH24:MI:SS'))-7,'FRIDAY') + 
 INTERVAL '23:59:59' HOUR TO SECOND AS range_end_dt

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

You should not depend on the locale-specific NLS settings.

You could use following functions:

  • NEXT_DAY
  • ADD_MONTHS
  • TRUNC

For example,

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

SQL> WITH t(created_dt) AS(
  2  SELECT to_date('04/08/2015 15:36','DD/MM/YYYY HH24:MI') FROM DUAL UNION ALL
  3  SELECT to_date('07/07/2015 15:32','DD/MM/YYYY HH24:MI') FROM DUAL
  4  )
  5  SELECT CREATED_DT,
  6    NEXT_DAY(TRUNC(add_months(created_dt, -1),'MM') -1,TO_CHAR(to_date('6','J'),'Day')) -1 start_date,
  7    NEXT_DAY(TRUNC(created_dt, 'MM')      -1, TO_CHAR(to_date('5','J'),'Day')) -1 + 0.99999 AS end_date
  8  FROM t;

CREATED_DT          START_DATE          END_DATE
------------------- ------------------- -------------------
04/08/2015 15:36:00 04/07/2015 00:00:00 31/07/2015 23:59:59
07/07/2015 15:32:00 06/06/2015 00:00:00 03/07/2015 23:59:59

SQL>

To get the time portion as 23:59:59, you could either add 0.99999 or subtract INTERVAL '1' SECOND. For example,

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> WITH t(created_dt) AS(
  2  SELECT to_date('04/08/2015 15:36','DD/MM/YYYY HH24:MI') FROM DUAL UNION ALL
  3  SELECT to_date('07/07/2015 15:32','DD/MM/YYYY HH24:MI') FROM DUAL
  4  )
  5  SELECT CREATED_DT,
  6    NEXT_DAY(TRUNC(add_months(created_dt, -1),'MM') -1,TO_CHAR(to_date('6','J'),'Day')) -1 start_date,
  7    NEXT_DAY(TRUNC(created_dt, 'MM')      -1, TO_CHAR(to_date('5','J'),'Day')) - (INTERVAL '1' SECOND)  AS end_date
  8  FROM t;

CREATED_DT          START_DATE          END_DATE
------------------- ------------------- -------------------
04/08/2015 15:36:00 04/07/2015 00:00:00 31/07/2015 23:59:59
07/07/2015 15:32:00 06/06/2015 00:00:00 03/07/2015 23:59:59

SQL>

Upvotes: 1

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Use Next_day function. The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than a date. 

select TO_DATE('04/08/2015 15:36' ,'DD/MM/YYYY hh24:mi') as created_date,
    next_day(ADD_MONTHS(TRUNC(TO_DATE('04/08/2015 15:36','DD/MM/YYYY hh24:mi')+1,'MM'),-1),'SATURDAY')
    as start_date,
    next_day(trunc(TO_DATE('04/08/2015 15:36','DD/MM/YYYY hh24:mi')-8)+0.99999 ,'FRIDAY')as end_date
     FROM DUAL

Instead of adding 0.99999 we can also achieve same thing with 1-(1/(24*60*60)) we are adding one day after that subtracting 1 part from 24*60*60 seconds.

Upvotes: 0

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

You can use some of the Date functions. I'm giving for sysdate. Use according to your requirement.

select NEXT_DAY(trunc((trunc (add_months (sysdate, -1), 'mm')), 'MONTH')-1, 'Saturday') as Start_date,
       NEXT_DAY(SYSDATE-8, 'FRIDAY') as End_date 
from dual;

Output

START_DATE  END_DATE
04-JUL-15   21-AUG-15

Upvotes: 0

Related Questions