Oracle Date Conversion Functions : Random Examples

 

Introduction:

Some random examples of Oracle PL/SQL Date Conversion functions. Might be useful for some and thought it worth sharing.

 

Examples:

Get current system date in default format (Easy !):

select sysdate from dual;

  

Get current system date in different formats:

select 'x ' || to_char(sysdate, 'yyyymmdd') from dual;
 
select 'x ' || to_char(sysdate, 'yyyymmdd hh24miss') from dual;
 
select 'x ' || to_char(sysdate, 'hh24miss') from dual;

 

Number of seconds since beginning of the day:

-- number of seconds since beginning of the day
select 'x ' || to_char(sysdate, 'sssss') from dual;

  

Number of days since beginning of the year:

-- number of days since beginning of year
select 'x ' || to_char(sysdate, 'ddd') from dual;
 

Get System Timestamp:

select systimestamp from dual;

This will return time up to 6 decimal places. Very useful for performance tuning/improvement as the time is recorded with microsecond accuracy.

Few other Timestamp functions/examples:

select 'x ' || to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4') from dual;
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff4') from dual;
 
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'missff6') from dual;
 

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, Date Function, Oracle, PL/SQL, Query, SQL, SQLPLUS