phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael McAllister <>
Subject Date math
Date Tue, 04 Aug 2015 19:14:49 GMT

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?



View raw message