giovedì 15 novembre 2012

Tip: How to get Today Date using Sunopsis Memory Engine

Today I needed to retrieve today's date in 'YYYYMMDD' using SUNOPSIS Memory Engine.
That would have been much easier to do the following:

FROM dual

but I wanted to be able to get a date in a string format without relying on any Oracle database schema. To get today's date using In-Memory Engine is quite tricky.
First, you have to create a procedure and make sure you add the following steps:

0 - Drop_Dual_Table
Simply drops a table named dual (which does not exist by default in the SUNOPSIS database)

10 - Create DUAL table

20 - Insert Values

Then you can assign today's date to a variable (and you can format it as you wish) and use it in your interfaces/packages.

P.D. Originally I wanted to get yesterday's date directly in SUNOPSIS MEMORY ENGINE, but I could not find a way to assign -1 offset to CURDATE(). Any ideas?