teradata get rid of hardcoded financial year date

Question

the current query is something like

SELECT field1, field2
FROM sometable
WHERE finished_date >'30/06/2014' or finished_date is null

Would like to have the '30/06/2014' not hardcoded in the view, so we don't need to update this and other views every time new financial year comes around.

Answer 1

oh...got it

SELECT field1, field2
FROM sometable
WHERE finished_date>'30/06/' || TRIM((EXTRACT (YEAR FROM ADD_MONTHS((ADD_MONTHS(date, 7)), -1)))-1 ) (date, format 'DD/MM/YYYY'))
OR (finished_date is null))