Q:

Oracle - How to Convert VARCHAR to DATETIME format

I am using Oracle10g database in which a table contains a Column with Date DataType. I am using the following query to get the record:
 select to_char(START_TIME, 'YYMMDD HH24:MI:SS') from table;

So from above query, the result will be of type VARCHAR. I have tried to_Date() method but resulted in displaying only DATE. Can i convert VARCHAR to DATETIME format? The result should be of type DATETIME. Please help me how to resolve this problem.

A:

an Oracle date contains both date and time so you can consider it a datetime (there is no datatype of datetime in Oracle). how is DISPLAYS when you select it is entirely up to your client. the default display setting is controlled by the NLS_DATE_FORMAT parameter. If you're just using the date in your pl/sql block then just assign it into a date datatype and select into that variable without to_char and it will work just fine and contain whatever time component is present in your table.
to control the display, for example using nls_date_format:
SQL> select a from datetest;
A
---------
19-FEB-13

SQL> alter session set nls_date_format='YYMMDD HH24:MI:SS';

Session altered.

SQL> select a from datetest;
A
---------------
130219 07:59:38

but again, this is only for display.

A:

Oracle's Date type fields contain date/time values, therefore converting it to Datetime does not make any sense (it's already datetime)
Read more about oracle date types here

