Not sure if this is in 4.2, but Phoenix supports casting a numeric to a date. You'd need to do this in two steps, though, as we support INTEGER -> BIGINT and then BIGINT -> DATE. like this:
SELECT CAST(CAST(col * 1000 AS BIGINT) AS DATE) ... A lot of date function were added in 4.4 that would help you if you need to extract the day/month/year. Perhaps you can backport them or upgrade? One more built-in that might help you is the TRUNC function which lets you "bucketize" based on HOUR, MINUTE, SECOND, DAY, etc. Thanks, James On Tue, Aug 4, 2015 at 12:14 PM, Michael McAllister < [email protected]> wrote: > 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 > > >
