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

A date/time function that returns the current local date and time.
{fn NOW}
{fn NOW()}
NOW takes no arguments and returns the current local date and time as data type TIMESTAMP. The argument parentheses are optional for the ODBC scalar syntax; they are mandatory for the SQL standard function syntax.
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.
You can use the CAST or CONVERT function to change the data type of timestamps, dates, and times.
Fractional Seconds of Precision
By default, NOW does not return fractional seconds of precision. It does not support a precision argument. However, by changing the system-wide default time precision, you can cause all NOW functions system-wide to return this configured number of digits of fractional second precision. The initial configuration setting of the system-wide default time precision is 0 (no fractional seconds); the highest setting is 9.
GETDATE is functionally identical to NOW, except that GETDATE provides a precision argument that allows you to override the system-wide default time precision; if you omit the precision argument, GETDATE takes the configured system-wide default time precision.
CURRENT_TIMESTAMP has two syntax forms: Without argument parentheses, CURRENT_TIMESTAMP is functionally identical to NOW. With argument parentheses, CURRENT_TIMESTAMP(precision), is functionally identical to GETDATE, except that the CURRENT_TIMESTAMP() precision argument is mandatory. CURRENT_TIMESTAMP() always returns its specified precision and ignores the configured system-wide default time precision.
Fractional seconds are always truncated, not rounded, to the specified precision.
Other Current Time and Date Functions
NOW, GETDATE, and CURRENT_TIMESTAMP all return the current local date and time, based on the local time zone setting.
GETUTCDATE returns the current Universal Time Constant (UTC) date and time as a timestamp. Because UTC time does not depend on the local timezone and is not subject to local time variants (such as Daylight Saving Time), this function is useful for applying consistent timestamps when users in different time zones access the same database. GETUTCDATE supports fractional seconds of precision. The current UTC timestamp is also provided by the Caché ObjectScript $ZTIMESTAMP special variable.
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. The TIME and DATE data types store their values as integers in $HOROLOG format. None of these functions support precision.
The following example shows the three syntax forms are equivalent; all return the current local date and time as a timestamp:
SELECT NOW(),{fn NOW},{fn NOW()}
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