How to convert date to datetime in Oracle?

sql oracle date datetime date-formatting
Joel Patrick Ndzie picture Joel Patrick Ndzie · Nov 25, 2015 · Viewed 68.9k times · Source

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')

Answer

Lalit Kumar B picture Lalit Kumar B · Nov 25, 2015

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

Related questions

Oracle: Avoiding NULL value in to_date

I have a functional select statement that has a where clause, in the where clause there is a statement like so... to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS') However, if …

Read More
Updating a date in Oracle SQL table

I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query: Select ASOFDATE from PASOFDATE; I get 4/16/2012 I tried running this query UPDATE PASOFDATE SET ASOFDATE = '11/21/2012'; but it …

Read More
Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-between-query https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle But it returned only null. sysdate is …

Read More