UniData Current Date Function

Question

Is there the equivalent of the sql server getdate() function in UniData? I'd like to be able to query a table like this:

SELECT GETDATE() AS EXPORT_DATE, * FROM TABLE

Answer 1

There are two ways to do this - one that is more "standard", and another more flexible but slightly awkward.

1) Create an I-descriptor that returns the current date like so:

:AE DICT TABLE EXPORT_DATE
001 I
002 DATE()
003 D4/
004
005 10R
006 S

And then

:LIST TABLE EXPORT_DATE

2) Using the Unidata "flavor" of the query language avoids creating a DICT item, but is sort of awkward to type and you need to be in ECLTYPE U (or use the command (list, sort) in lower case to force ECLTYPE U):

:list TABLE EVAL "OCONV(DATE(),'D4/')" COL.HDG "EXPORT_DATE"

Personally, I almost always have a TODAY, COUNTER, CURR.USER etc. DICT item in the main tables used for reporting. You can think of I-Descriptors as little mini-views from a SQL perspective, that only return one column. Here's an interesting set of examples on the possible date formats to return too.

Answer 2

:AE VOC TODAY
001:  I
002:  DATE()
003:  D4/
004:
005:  10R
006:  S

By putting it in the VOC, it is now accessible to all files defined in that environment.

You can now LIST TRANSACTIONS WITH TRAN.DATE = TODAY

This will compare a field in the TRANSACTIONS file, TRAN.DATE to the date it is today, which is returned from the date function DATE(). All Pick languages, which UniData is, uses an internal date, which is how many days past 12/31/1967 (The year Dick Pick's daughter was born) The conversion which is on line 3, "D4/" converts that internal date to a MM/DD/CCYY format when displayed. Changing the "/" to a "-" makes it look like MM-DD-CCYY, and changing the 4 to a 2 will change the dates to display with only the significant last 2 digit.