Oracle SQL help - Financial Year 1

[SmpTraining]

Hi All,

I am trying to get an SQL WHERE clause to select records for the current finacial year only. Therefore it needs to take the 'sysdate' and, depending on the month, create a query against a date of either

A) my_date >= 01/04/YYYY and my_date <= 31/03/YYYY+1

OR

B) my_date >= 01/04/YYYY-1 and my_date <= 31/03/YYYY

I'm using UK 'DD/MM/YYYY' date formats and case A above should be used if the current month is >= 4, otherwise case B should be used. YYYY is, of course, the sysdate year value.

I hope this makes sense. I have tried various things but am not great at Oracle SQL I'm afraid. Any help would be much appreciated.

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[hoinz]

Hi Steve,

a lot of solutions should be possible, I think.
One of them:

... where trunc(my_date) between
add_months(trunc(add_months(sysdate,-3),'y'),3)
and
add_months(trunc(add_months(sysdate,-3),'y'),15)

regards

 

[SmpTraining]

Very elegant hoinz. Thank you.

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[hoinz]

glad it helped, thanks

 

[hoinz]

Hi Steve,

sorry, it seems my solution is not quite correct;
it will include 1st of April 2006 in the current fiscal year.

Don't trust anybody who seems to give elegant solutions

better should be:
... where trunc(my_date) >= add_months(trunc(add_months(sysdate,-3),'y'),3)
and trunc (my_date) < add_months(trunc(add_months(sysdate,-3),'y'),15)

 

[SmpTraining]

I was going to do some testing on the last date in the range as I wasn't sure it was going to work - looks like you've saved me some more time!

(I can't give you another star though!)

Thanks again

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk