Convert hours to Date/Timestamp in oracle

 Convert Year to Date in oracle

            If user has number between 00 to 23,That number can be converted to timestamp .

Syntax:

           to_timestamp(hour,fmt)  

        

24 hour format 00 to 23

SQL> select to_timestamp('23','hh24') from dual;

TO_TIMESTAMP('23','HH24')
-------------------------------------------------
01-MAY-14 11.00.00.000000000 PM

 

It has current date and time specified in the format.

 

SQL> select to_timestamp('1','hh') from dual;

TO_TIMESTAMP('1','HH')
-----------------------------------------------
01-MAY-13 01.00.00.000000000 AM

In 12 hours format  ,hour should be between 1 and 12.

In 24 hours format, hour should be between 00 and 23.

otherwise following error messages will be displayed.

ERROR at line 1:
ORA-01849: hour must be between 1 and 12

 

ERROR at line 1:
ORA-01850: hour must be between 0 and 23

Tags: Convert hour    to date/timestamp,convert hour    to Date/timestamp using To_timestamp function,oracle convert hour    to date ,oracle convert hour    to date/timestamp using to_date,oracle convert hour    to current hour   date/timestamp.