Caché SQL Reference
NOW
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A date/time function that returns the current local date and time.
Synopsis
NOW()
{fn NOW}
{fn NOW()}
Description
NOW takes no arguments. 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 as a timestamp; it adjusts for local time variants, such as Daylight Saving Time.
NOW returns a timestamp in %TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff).
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.
SYSDATE is functionally identical to the argumentless CURRENT_TIMESTAMP function.
Other Current Time and Date Functions
NOW, GETDATE, CURRENT_TIMESTAMP, and SYSDATE 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 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.
Examples
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) timestamps:
  &sql(SELECT NOW(),GETUTCDATE() INTO :a,:b)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"Local timestamp is:  ",a
    WRITE !,"UTC timestamp is:    ",b
    WRITE !,"$ZTIMESTAMP is:      ",$ZDATETIME($ZTIMESTAMP,3,,3)
 }
 
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