Trying to automate the date range 1

[jcarrott]

I am using a PL/SQL script to extract data and I am currently using,

   and I.MATCH_DATE    >= to_date('09/01/2011', 'MM/DD/YYYY')
   and I.MATCH_DATE     < to_date('10/01/2011', 'MM/DD/YYYY')

The second month is the current month and the starting month is last month. Can this be coded to automatically generate the dates?

 

[ggriffit]

check out

http://psoug.org/reference/date_func.html

which gives you a method for getting these.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[Dagon]

Something like:

select trunc(add_months(sysdate,-1), 'mm')+8,
       trunc(sysdate, 'mm') + 9
 from (select to_date('01/10/2011', 'DD/MM/YYYY') as match_date from dual) i
where I.MATCH_DATE    >= trunc(add_months(sysdate,-1), 'mm')+8
   and I.MATCH_DATE     < trunc(sysdate, 'mm') + 9

 

[jcarrott]

I guess I communicated poorly, I am trying to get the current date and compute the first day of the preivous month and the first day of this month.

I was running into issues where the current month is January

 

[Dagon]

That's even easier. Just use:

trunc(add_months(sysdate,-1), 'mm')
trunc(sysdate, 'mm')

 

[jcarrott]

Will this give me the first day of each month?

For example today is 10/24/2011. I need the first date to be 9/1/2011 and the second to be 10/1/2011. This way I am testing for all values greater than or equal to 9/1/2011 and less than 10/1/2011. This way I don't have to worry about what the last day of the previous month was.

 

[Dagon]

Yes, trunc(<date>, 'mm'> always gives the first day of each month.

 

[Beilstwh]

However if you did want to check between the start and end dates of the last month then

between trunc(add_months(sysdate,-1),'mm') and last_day(trunc(add_months(sysdate,-1)))

Bill
Lead Application Developer
New York State, USA