Re: sql question

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Tue, 05 Jan 2010 14:41:18 -0500
Message-ID: <4B4395DE.7090504_at_tufts.edu>

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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 05 2010 - 13:41:18 CST