Caché SQL Reference
[Back] [Next]
Go to:

A scalar date/time function that returns the current local date and time.
{fn NOW()}
{fn NOW}
NOW takes no arguments and returns the date and time as type TIMESTAMP. Note that the argument parentheses are optional. NOW returns the current local date and time for this timezone; it adjusts for local time variants, such as Daylight Saving Time.
It returns the current date and time in the format:
yyyy-mm-dd hh:mm:ss
Hours are represented in 24–hour format. Leading zeros are retained for all fields.
To change the default datetime string format, use the SET OPTION command with the various date and time options.
The CURRENT_TIMESTAMP and GETDATE functions can also be used to return the current local date and time. These functions supports precision of fractional seconds; NOW does not support precision. These functions all use the TIMESTAMP data type.
To return just the current date, use CURDATE or CURRENT_DATE. To return just the current time, use CURRENT_TIME or CURTIME. The functions use the DATE or TIME data type. None of these functions support precision.
Note that all Caché SQL time and date functions except GETUTCDATE are specific to the local time zone setting. To get a current timestamp that is universal (independent of time zone) you can use GETUTCDATE or the Caché ObjectScript $ZTIMESTAMP special variable.
These data types perform differently when using embedded SQL. A TIMESTAMP data type stores and displays its value in the same format. The TIME and DATE data types store their values as integers in $HOROLOG format; when displayed in SQL they are converted to date or time display format; when returned from embedded SQL they are returned as integers. You can use the CAST or CONVERT function to change the datatype of dates and times.
The following examples both return the current system date and time:
SELECT {fn NOW()} AS DateTime
SELECT {fn NOW} AS DateTime
The following Embedded SQL example compares local (time zone specific) and universal (time zone independent) time stamps:
  &sql(SELECT {fn NOW} INTO :a)
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    IF a=b {WRITE "local and UTC time are the same: ",a,!}
    ELSE {WRITE !,"SQL Now is:    ",a
          WRITE !,"ZTimestamp is: ",b }
The following example sets the LastUpdate field in the selected row of the Orders table to the current system date and time:
UPDATE Orders SET LastUpdate = {fn NOW()}
  WHERE Orders.OrderNumber=:ord
See Also