ElevateDB: Date Math 1

ElevateDB has just impressed me again the way it handles date math. I don't know if you have ever tried doing SQL Date Math but it can be tricky unless there is built in support.

Often times you need to add a Day or a Month or a Year to a given date. ElevateDB lets you do this and it works well.

I opened up a new SQL Window inside EDB Manager and tried executing the following:

SELECT
Current_Date as Today

I received an Error:

ElevateDB Error #700 An error was found in the statement at line 3 and column 1 (Missing FROM)

So I created a dummy table with one field and one record. Then I modified my original SQL as follows...

SELECT
Current_Date as Today,
FROM dummy

and it works great. Even though I'm not returning any real data from the "dummy" table.

Being inquisitive and wanting to see how well ElevateDB does "Date Math" I tried the following...

SELECT
Current_Date + INTERVAL '-4' DAY AS FourDaysAgo,
Current_Date + INTERVAL '-3' DAY AS ThreeDaysAgo,
Current_Date + INTERVAL '-2' DAY AS TwoDaysAgo,
Current_Date + INTERVAL '-1' DAY AS OneDayAgo,
Current_Date as Today,
Current_Date + INTERVAL '1' DAY AS OneDayFromNow,
Current_Date + INTERVAL '2' DAY AS TwoDaysFromNow,
Current_Date + INTERVAL '3' DAY AS ThreeDaysFromNow,
Current_Date + INTERVAL '4' DAY AS FourDaysFromNow
FROM dummy

and it works AWESOME. I tested going backwards until one day before March 1, 2011 testing leap year. I tested going forward until one day after Feb 28, 2012. I tested December 31st plus 1 Day and Jan 1st minus 1 Day. It's all good.

Now I know that using a dummy table this way probably isn't considered EDB best practices but it did allow me to get the information I was looking for. This method also works for selecting literal values.

I'm waiting to hear back from the ElevateDB boys on the proper way to return this kind of data but until then, I have a working model.

Semper Fi
Gunny Mike