2. SQL Data Types : Constants
 
Share this page                  
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_DATE can be referenced in an INSERT statement that inserts many rows, or an UPDATE statement that alters many rows, and the same date 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:
Constant
Meaning
NULL
Indicates a missing or unknown value in a table.
CURRENT_DATE
Current date (as ANSI date type)
CURRENT_TIME
Current time with time zone
CURRENT_TIMESTAMP
Current date and time with time zone
CURRENT_USER
Same as user
INITIAL_USER
Vector user identifier in effect at the start of the session
LOCAL_TIME
Current time without time zone
LOCAL_TIMESTAMP
Current timestamp without time zone
SESSION_USER
Same as user
SYSDATE
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, this function 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
SYSTEM_USER
Operating system user identifier of the user who started the session
USER
Effective user of the session (the Vector user identifier, not the operating system user identifier)