[SOLVED] Universe : SQL Statement with dates to YYYYMMDD string

Universe : SQL Statement with dates to YYYYMMDD string

I am trying to do that kind of SQL query on a Universe Rocket U2 System:

SELECT id
FROM bars
WHERE DATE() BETWEEN DATE(starting_date) - 3 AND DATE(ending_date) + 3;

However, my starting_date and my ending_date are stored in "YYYYMMDD" strings, I can't make that query works, I'm unable to find date functions allowing me to do that. I've found CURRENT_DATE replacing DATE() but I can't find how to convert my YYYYMMDD to dates.

Thanks

Solution

This RetrieVe expression will return the current date as YYYYMMDD.

OCONV(@DATE, "DYMD[,2,2]"); FIELD(@1, " ", 1):FIELD(@1, " ", 2):FIELD(@1, " ", 3)

Note I have added the format to include the month and day as two digits, something I missed in the SQL of my first post. Four digits is the default for Year so it can be omitted but the comma is required for holding the place.

BTW If you ever need the current date as YYYY-MM-DD the CONV format is:

D-YMD[,2,2]

(Any non-numeric character can be used in the location of the dash.)

You can include ad hoc RetrieVe expressions in SQL by using EVAL.

SELECT EVAL 'OCONV(@DATE, "DYMD[,2,2]"); FIELD(@1, " ", 1):FIELD(@1, " ", 2):FIELD(@1, " ", 3)' AS alias FROM sometable

To use EVAL in a WHERE clause you need to select and alias it then refer to the alias in the WHERE clause.