Re: Select query using to_date

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 12 Aug 2004 19:10:37 +0200
Message-ID: <411ba428$0$7222$626a14ce@news.free.fr>

"George" <georgina.wren_at_eu.watsonwyatt.com> a écrit dans le message de news:d45230d0.0408120316.5ca78f1f_at_posting.google.com...
> Hi,
>
> I'm trying to run a select query with a date parameter. The
> 'Tran_date' needs to be between two dates and I have a 'case'
> condition that determines these dates:
>
> and a.tran_date between
> Case when (extract(month from sysdate)) > 10
> then to_date('01/10' || to_number(sysdate, 'YYYY'), 'DD/MM/YYYY')
> else to_date('01/10' || to_number(sysdate, 'YYYY'-1), 'DD/MM/YYYY')
> End
> and sysdate
>
> If the sysdate month is greater that 10 then the date is '01/10' and
> the Year needs to be the current year. Else the date is '01/10' and
> the year is the previous year. I keep getting an 'Invalid number'
> error.
>
> Can anyone help with this?
>
> Thanks in advance
> George

Just a hint of what you can do:

SQL> select sysdate,

  2         decode(sign(sysdate-add_months(trunc(sysdate,'YEAR'),9)),
  3                -1, add_months(trunc(sysdate,'YEAR'),-3),
  4                add_months(trunc(sysdate,'YEAR'),9)) "For sysdate",
  5         sysdate+60 "sysdate+60",
  6         decode(sign((sysdate+60)-add_months(trunc((sysdate+60),'YEAR'),9)),
  7                -1, add_months(trunc((sysdate+60),'YEAR'),-3),
  8                add_months(trunc((sysdate+60),'YEAR'),9)) "For sysdate+60"

  9 from dual
 10 /

SYSDATE For sysdate sysdate+60 For sysdate+60 -------------- -------------- -------------- -------------- 12/08/2004 01/10/2003 11/10/2004 01/10/2004

-- 
Regards
Michel Cadot

Received on Thu Aug 12 2004 - 12:10:37 CDT