CODEWITHSUNDEEP
sqloracle-databasedaterange
Rob Morris
Rob Morris

Reputation: 137

Show all jobs from 1st April to end of previous month

I'm looking to set up some reports to show financial year - end of previous month information but I'm coming stuck when trying to set up the date filter. eg if I ran the report now it would show all jobs on the system from 1st April 2019 - 31st Jan 2020.

dummy code I've got is:

select
job.job_number,
job.job_logged_date,
job.actual_comp_date,
job.job_type

from
job

where

It's just the WHERE section I'm having problems with.

I know for previous months I can use 

where job.actual_comp_date >= trunc(trunc(SYSDATE,'MM')-1,'MM') AND job.actual_comp_date <=trunc(SYSDATE,'MM')

but I can't remember how to change this to say from a specific date (eg 1st April 2019) to end of previous month.

Upvotes: 1

Views: 63

Answers (5)

Ergi Nushi
Ergi Nushi

Reputation: 863

Try:

where job.actual_comp_date > to_date('01-04-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
and job.actual_comp_date < to_date('31-01-2020 23:59:59', 'DD-MM-YYYY HH24:MI:SS')

If you want the date to be the last day of previous month you can try:

where job.actual_comp_date > to_date('01-04-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
and job.actual_comp_date < last_day(add_months(sysdate,-1))

Upvotes: 1

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Also 

WITH da(date_h) AS (
SELECT '2019-12-30'  date_h FROM dual union all
SELECT '2018-12-30'  date_h FROM dual union all
SELECT '2020-02-20'  date_h FROM dual union all
SELECT '2019-08-30'  date_h FROM dual  
)
SELECT date_h FROM da WHERE 
to_date(date_h, 'yyyy-mm-dd') >= to_date('2019-04-01','yyyy-mm-dd') 
AND 
to_date(date_h, 'yyyy-mm-dd') <= LAST_DAY(ADD_MONTHS(sysdate, -1)) --to_date('2020-01-31','yyyy-mm-dd')

db <> fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I think you just want trunc():

where job.actual_comp_date < trunc(SYSDATE, 'MON') and
      job.actual_comp_date >= date '2019-04-01'

If you want the current fiscal year to be based on the current date, then:

where job.actual_comp_date < trunc(SYSDATE, 'MON') and
      job.actual_comp_date >= add_months(trunc(add_months(sysdate, -3), 'YYYY'), 3)

Upvotes: 1

Popeye
Popeye

Reputation: 35930

You can use the following logic:

where job.actual_comp_date >= trunc(trunc(sysdate) - interval '3' month, 'year') 
                                      + interval '3' month
AND job.actual_comp_date < trunc(trunc(sysdate) - interval '3' month, 'year') 
                                      + interval '15' month ;

Cheers!!

Upvotes: 0

gwatene
gwatene

Reputation: 81

you can try below

where job.actual_comp_date >= to_date('2019-12-30','yyyy-mm-dd') AND job.actual_comp_date <=LAST_DAY(ADD_MONTHS(sysdate, -1))

Upvotes: 0

Related Questions