0

How can I convert two varchar fields in Oracle to a datetime type? I have one field for date ('2014-07-19') and a field for time ('08:00'), I need this to be like '2014-07-19 08:00' in datetime Oracle.

heliosk
  • 1,113
  • 3
  • 23
  • 45

1 Answers1

1

Do this: 

TO_DATE(DATE_COLUMN||TIME_COLUMN, 'yyyy-mm-ddHH24:mi')
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • SELECT TO_DATE(DATE_INIT||HOUR_INIT, 'yyyy-mm-ddHH24:mi') NEWDATE FROM CF_TB. I executed this query but it just returns the date (01-JAN-14) without the hour. – heliosk Jul 19 '14 at 15:37
  • Read my answer [here](http://stackoverflow.com/questions/15970277/comparing-with-date-in-oracle-sql/15970516#15970516) from _"Your second problem"_ @heliosk. – Ben Jul 19 '14 at 16:10
  • @heliosk - that's due to your default time format. Try this: `alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";` and then try it again. Or set it directly in your query: `TO_CHAR(TO_DATE(DATE_COLUMN||TIME_COLUMN, 'yyyy-mm-ddHH24:mi'), 'YYYY-MM-DD HH242:MI:SS')` – Wernfried Domscheit Jul 19 '14 at 20:51
  • Thanks! Wernfried. It worked, it was a simple step then. – heliosk Jul 19 '14 at 21:41