docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Functions  /  NOW


InterSystems SQL Reference
NOW
[Back]  [Next] 
InterSystems: The power behind what matters   
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 can return a timestamp in either %TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) or %PosixTime data type format (an encoded 64-bit signed integer). The following rules determine which timestamp format is returned:
  1. If the current timestamp is being supplied to a field of data type %PosixTime, the current timestamp value is returned in POSIXTIME data type format. For example, WHERE PosixField=NOW() or INSERT INTO MyTable (PosixField) VALUES (NOW()).
  2. If the current timestamp is being supplied to a field of data type %TimeStamp, the current timestamp value is returned in TIMESTAMP data type format (yyyy-mm-dd hh:mm:ss). Its ODBC type is TIMESTAMP, LENGTH is 16, and PRECISION is 19. Hours are represented in 24–hour format. Leading zeros are retained for all fields. For example, WHERE TSField=NOW() or INSERT INTO MyTable (TSField) VALUES (NOW()).
  3. If the current timestamp is being supplied without context, the current timestamp value is returned in TIMESTAMP data type format. For example, SELECT NOW().
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