Oracle SYSDATE is a pseudo column which returns the date and time as per database. Oracle SYSDATE Format for time and date depends on the value of NLS_DATE_FORMAT parameter. Oracle SQL SYSDATE Function does not require any parameter.Also, Oracle TRUNC SYSDATE query can be written to remove the time part from Oracle SQL SYSDATE and SYSDATE-1 Oracle query will return date of previous day.
Oracle SYSDATE Function Syntax
SELECT SYSDATE FROM table_name;
Oracle SYSDATE – Simple Usage Example
We can use SQL SYSDATE pseudo column to fetch the date and time of the database.
For example, the SQL SYSDATE query below returns the database’s date and time
SELECT SYSDATE FROM dual;
Above SQL SYSDATE query returns current date and time from local database e.g. “11/1/2012 4:36:50 PM”
Oracle SQL SYSDATE – Using SYSDATE-1 Oracle Example
Oracle SQL SYSDATE can be used with the + and – signs.
For example, we can use SYSDATE-1 Oracle query in SQL SELECT Statement to get the date of previous day.
Usage of SYSDATE-1 Oracle query can be seen below”
SELECT SYSDATE-1 "SYSDATE-1 Oracle" FROM dual;
Above SYSDATE-1 Oracle query returns the date and time of previous day.
As we have written SYSDATE-1 Oracle query, we can also write SYSDATE-2, SYSDATE-3, SYSDATE+1, SYSDATE+2 and so on.
Note: We have aliased SYSDATE-1 as SYSDATE-1 Oracle.
Oracle SQL SYSDATE – Oracle TRUNC SYSDATE Example
Oracle TRUNC SYSDATE query allows to view only those parts of SYSDATE which we wish to view.
For example, below Oracle TRUNC SYSDATE query returns the date part and not the time part from Oracle SQL SYSDATE Function
SELECT TRUNC(SYSDATE) "Oracle TRUNC SYSDATE" FROM dual;
Above Oracle TRUNC SYSDATE query returns the system date and not the time e.g. “11/1/2012”.
Note: We have aliased TRUNC(SYSDATE) as Oracle TRUNC SYSDATE.