How to convert date to datetime in Oracle?

Question

I have a date in oracle with this DD-MM-YYY format and I want to convert it to datetime with this other DD-MM-YYY HH24:MI format, how can I continue?

I tried this but nothing works:

 to_date(the_date,'DD-MM-YYY HH24:MI') 

as well as this:

 to_date(to_char(date_debut_p),'DD-MM-YYY HH24:MI') 

Answer 1

I have a date in oracle with this format DD-MM-YYY and I want to convert it to datetime in this other format DD-MM-YYY HH24: MI

No, you are confused. Oracle does not save dates in the format you see . It is stored inside 7 bytes with each byte storing different components of the datetime value.

A DATE data type always has date and time elements accurate to seconds .. p>

If you want to display, use TO_CHAR with the appropriate FORMAT .

For instance,

 SQL> select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'MM ------------------- 11/25/2015 22:25:42 

Answer 2

Oracle DATE ALWAYS data type contains (stores) time.

If you want to see it, you can use the TO_CHAR function.

If you want to add, for example, 1 hour, you can simply use date_debut_p+1/24 .

Answer 3

If you want to convert to a timestamp, you can do the following:

Select to_timestamp (date_column, 'DD-MM-YYY') from the table;

However, if you want the format you want, you can do the following:

Select to_char (to_timestamp (date_column, 'DD-MON-YY'), 'DD-MM-YYY HH24: MI') from the table;

Hope this helps ..