How to convert date to datetime in Oracle?
Solution 1
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
Solution 2
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
.
Solution 3
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..
Joel Patrick Ndzie
Updated on September 21, 2020Comments
-
Joel Patrick Ndzie over 3 years
i have a date in oracle with this format
DD-MM-YYY
and i want to convert it to datetime with this other formatDD-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')
-
Joel Patrick Ndzie over 8 yearsto_char('09/01/15') returns me 09/01/15
-
Tatiana over 8 years@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')