Hi I have a table with a date stored in it. The date is always the last day of a month. For example, 31-AUG-2015 or 28-FEB-2015. I have the date stored in two separate columns. In one place it is an integer (20150831 and 20150228). In another place it uses the DATE datatype.
I want to write a SQL statement where, given a date (ignore which datatype at the moment), I can return rows for that date, as well as the same date last year, all in one year. The kicker is that we have to interpret the date as a month end, so we have to factor leap years and February the 29th in. If we didn't have to deal with the leap year the SQL could be as simple as:- SELECT <columns> FROM <table> WHERE datenum in (<datenum>,<datenum>-10000); However, this won't work for 29-FEB-2016 as (20160229-10000 = 20150229) Additionally, we're on Phoenix 4.2, so we don't have access to UDFs. So ... any ideas how to resolve this query? Is there some built in date math available to me that I can't find in the documentation online? Regards Mike
