Universe : SQL Statement with dates to YYYYMMDD string

Question

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

Answer 1

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.

Answer 2

Here are some leads. Have not got time to work it all out right now.

This will express CURRENT_DATE as YYYY MM DD

CURRENT_DATE CONV 'DYMD'

The SUBSTRING function will return parts of a string.

SUBSTRING('starting_date' FROM 1 FOR 4)

The Universe SQL concatenation operator is a double pipe

||

Better to convert CURRENT_DATE to YYYYMMDD as this will avoid having to process every record in the table.

You would have a better time working with the more powerful RetrieVe expressions. I'll come back with that tomorrow.