How to convert date to datetime in Oracle?

Question

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

I've tried this but nothing is working :

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

and also this:

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

http://stackoverflow.com/questions/9180014/using-oracle-to-date-function-for-date-string-with-milliseconds — are, Nov 25 '15 at 16:34
`to_ CHAR`(the_date,'DD-MM-YYY HH24:MI')` assuming `the_date` is a date. — Shannon Severance, Nov 25 '15 at 17:15

score 24 · Answer 1 · edited May 23 '17 at 12:17

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

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

DATE data type always has both date and time elements up to a precision of seconds.

If you want to display, use TO_CHAR with proper FORMAT MODEL.

For example,

SQL> select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'MM
-------------------
11/25/2015 22:25:42

score 3 · Answer 2 · answered Nov 25 '15 at 16:36

3

Oracle DATE datatype ALWAYS contains (stores) time.

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

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

answered Nov 25 '15 at 16:36

Tatiana

1,489
10
19

to_char('09/01/15') returns me 09/01/15 – Joel Patrick Ndzie Nov 25 '15 at 16:44
@JoelPatrickNdzie '09/01/15' is alerady char! You should take your date field and make `to_char(date_fiekd, 'dd.mm.yyyy hh24:mi:ss')` – Tatiana Nov 25 '15 at 19:37

score 2 · Answer 3 · answered Sep 20 '19 at 19:08

If you want to covert to timestamp, you can do the following:

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

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

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

Hope it helps..

How to convert date to datetime in Oracle?

3 Answers3