3. Understanding SQL Data Types : SQL Constants
 
Share this page                  
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.
For example:
SELECT DATE('NOW');

INSERT INTO sales_order
        (item_number, clerk, billing_date)
        VALUES ('123', USER, DATE('TODAY')+DATE('7 days'));
UPDATE employee SET e_sal = e_sal * 1.05, e_udp_time = CURRENT_TIMESTAMP WHERE e_stat = 'x';
The following constants can be used in queries:
Special Constant
Meaning
NOW
Current date and time. This constant must be specified in quotes.
This constant only works when used within the SQL DATE() function.
NULL
Indicates a missing or unknown value in a table.
TODAY
Current date. This constant must be specified in quotes.
This constant only works when used within the SQL DATE() function.
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
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