SQL Reference Guide > SQL Reference Guide > Understanding SQL Data Types > SQL Constants
Was this helpful?
SQL Constants
A constant is a symbol that represents a specific data value.
SQL constants can be used in queries and expressions. They can be used any number of times in a query, but the value is only materialized once per query execution. So a constant such as CURRENT_TIME can be referenced in an INSERT statement that inserts many rows, or an UPDATE statement that alters many rows, and the same time value will be used for each.
Examples:
SELECT CURRENT_DATE;
SELECT CURRENT_DATE + 7;
CREATE TABLE sales_order (item_number INT, clerk VARCHAR(32), billing_date ANSIDATE);
INSERT INTO sales_order (item_number, clerk, billing_date) VALUES ('123', USER, CURRENT_DATE+DATE('7 days'));
The following constants can be used in queries:
Special Constant
Meaning
NOW
Current date and time. This constant must be specified in quotes.
Note:  This constant only works when used within the SQL DATE() function.
Note:  Not valid in X100 tables.
NULL
Indicates a missing or unknown value in a table.
TODAY
Current date. This constant must be specified in quotes.
Note:  This constant only works when used within the SQL DATE() function.
Note:  Not valid in X100 tables.
CURRENT_DATE
Current date (as ANSI date type)
CURRENT_TIME
Current time
CURRENT_TIMESTAMP
Current date and time
LOCAL_TIME
Current time without time zone
LOCAL_TIMESTAMP
Current date and time without time zone
TIMESTAMP_UNIX
Number of seconds that have elapsed since 1 January 1970, as returned by UNIX_TIMESTAMP (INT4)
USER
Effective user of the session (the Ingres user identifier, not the operating system user identifier)
CURRENT_USER
Same as user
SYSTEM_USER
Operating system user identifier of the user who started the session
INITIAL_USER
Ingres user identifier in effect at the start of the session
SESSION_USER
Same as user
SYSDATE
Returns the current date and time set for the operating system on which the database resides. The format returned depends on the default TIMESTAMP format.
In distributed SQL statements, returns the date and time set for the operating system of your local database.
When not used inside a TO_CHAR() wrapper function, SYSDATE always returns a date in the default date format.
Examples:
SELECT SYSDATE\g
2013-08-06 22:28:35.784654-07:00
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')\g
08-06-2013 22:29:58
Last modified date: 01/30/2023