Need Help on where clause

[LoriDurham]

WHERE DATE(LPAY.ADDED_DT) between date( '07' || '-' || '01' || '-' || CHAR(YEAR(current date )))
and (((DATE(current date) - Day(DATE(current date)) day)+1 day)) - 1 day

Above is my where clause which works fine, I alwuas wants to get data from July to June and chnages next year
But problem here is after December year will change to 2010,
can i write a case statement in a way that year will only increment after june(fiscal year time period)?
Thanks so much

 

[fredericofonseca]

if I get it correctly you wish to retrieve data as follows.

if current month is less than july you wish to retrieve all data from july previous year till current date. else you wish to retrieve all data from july current year until current date.

if so then following may give you what you want (untested)

replace "CHAR(YEAR(current date ))" with
CHAR(
case
when MONTH(current date) < 7
then YEAR(current date ) - 1
else YEAR(current date )
end)




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886