query to check database startup time in oracle

The other way is to use the TO_CHAR function to change the format. ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' To show the time component of this value, either use the TO_CHAR function or alter your session to include the time format. Just like with the SYSDATE function, this function returns a DATE data type, which actually includes a date and a time. This shows the current date of your user session. No brackets are needed as there are no parameters. To use this function, simply add the word CURRENT_DATE to your query. This can sometimes be different to the database timezone. This is the timezone that your session is in, or the timezone you’re in when you log in to the system. The CURRENT_DATE function is similar to the SYSDATE function, but it shows you the current date and time in the session timezone. You can see that the output now includes the date and time. TO_CHAR(SYSDATE, 'DD/MM/YYYY HH:MI:SS') AS sysdate_time

We could use the TO_CHAR function to change the way this particular function is shown: SELECT We changed the date format in the session to include the time. SELECT SYSDATEĪs you can see, the output now shows the date and time returned from SYSDATE. Now, let’s rerun the SELECT statement with the SYSDATE function. SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' We can see this if we change the session’s date format, or surround the SYSDATE in a TO_CHAR function. The SYSDATE function does return the time component as well though. This is because the output format for SELECT queries for dates is currently set to the DD/MM/YYYY format. I’ve used the DUAL table because Oracle needs to select from a table in a select query, and the DUAL table is useful for this (read more about the dual table here).Īs you can see, the current date is shown. It has no parameters, which means you don’t need any brackets after it.Īn example of the SYSDATE function is: SELECT SYSDATE To use it, you simply type the word SYSDATE. It shows the date and time of the database server. The Oracle SYSDATE function allows you to easily output the current date. So now we’ve looked at the different data types, let’s take a look at the different Oracle date functions. INTERVAL DAY TO SECOND: This data type also stores a period of time, but stores a value that captures seconds all the way up to days.INTERVAL YEAR TO MONTH: This data type stores a “period of time” (rather than a “point in time” like other data types), and represents a period of time in months up to years.TIMESTAMP WITH LOCAL TIME ZONE: This data type is similar to TIMESTAMP WITH TIME ZONE, but the timezone that’s stored is the database timezone.TIMESTAMP WITH TIME ZONE: This data type is the same as TIMESTAMP, but it stores the timezone along with it.

TIMESTAMP: This data type stores year, month, day, hour, minute, second, as well as fractional seconds.Yes, even though it’s called “date”, it stores the time component. It stores year, month, day, as well as hour, minute and second. DATE: The “standard” date value in Oracle.There are a few different data types in Oracle that store date values.

Query to check database startup time in oracle