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:

SELECT TO_CHAR(sysdate,'YYYYMMDD')
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?