RE: sql question
- From: "Blanchard, William" <wblanchard@xxxxxxxxxxxxxxxxxxxx>
- To: <joan.hsieh@xxxxxxxxx>, "oracle_l" <ORACLE-L@xxxxxxxxxxxxx>
- Date: Tue, 5 Jan 2010 13:48:09 -0600
This is what I use to get the previous month.
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY/MM') FROM dual;
WGB
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joan Hsieh
Sent: Tuesday, January 05, 2010 1:41 PM
To: oracle_l
Subject: Re: sql question
there is some confusion with the question. My question is how to always
get the last month of the data? The job was set to run on 2010/1/15,
then I need to get 2009/12 in the where clause.
Thanks again,
Joan
Joan Hsieh wrote:
> Hi,
>
> I am trying to modify a query with where clause like
> '&&year%%month%'to retrieve all the data by each month of the year. I
> have managed this query to set up automatically run on the 15th of the
> month without any problem until it turned the year of 2010.
>
> I used select substr(add_months(sysdate,-1),5,2) from dual to collect
> the month.
> and select substr(sysdate,1,4) from dual to get the year.
>
> The problem is I can't get the data on this month cause I will
> generate the date like '201012%'.
>
> Do you know how to handle this? Using if statement? if the month is
> 12, then the year should be -1? I am not sure how to accomplish this.
>
> Thanks,
>
> Joan
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
