Convert string to date in Oracle SQL

Question

I'm trying to convert string column to date in Oracle SQL.

Here is my an example of value from the data:

'03/12/14 11:00:00'

Here is my query:

select to_date(A,'DD/MM/YY HH24:MI:SS')
from MyTable

Here is an example of the output:

03-DEC-14

Why my query return only the date but not the hour?

Because a date doesn't have an hours part? Try TIMESTAMP, maybe it works better. — jarlh, Feb 03 '15 at 15:39
possible duplicate of [Convert a string date into datetime in Oracle](http://stackoverflow.com/questions/7920637/convert-a-string-date-into-datetime-in-oracle) — Siyual, Feb 03 '15 at 15:39
@a_horse_with_no_name. Thanks for the info! (I'm not an Oracle user, but I believe you've already found that out!) — jarlh, Feb 03 '15 at 15:43

score 3 · Accepted Answer · answered Feb 03 '15 at 15:45

Assuming you are using SQL*Plus (or SQL Developer) the default NLS_DATE_FORMAT is applied when a DATE value is displayed. To verify your current format you can run:

select value 
from nls_session_parameters 
where parameter = 'NLS_DATE_FORMAT';

To adjust this, run:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

Then you should see the time as well.

score 1 · Answer 2 · answered Feb 03 '15 at 15:45

You are trying to DISPLAY the datetime value. Use TO_CHAR along with proper FORMAT MODEL.

For example,

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

A DATE consists of both date and time portions. You just need to make sure you use appropriate format model to display it. Else, you end up with a format which depends on your locale-specific NLS settings.

score 0 · Answer 3 · answered Feb 03 '15 at 15:43

0

You can use timestamp

select to_timestamp(A, 'DD/MM/YY HH24:MI:SS') from MyTable

answered Feb 03 '15 at 15:43

Onur Cete

263
1
2
10

score 0 · Answer 4 · answered Feb 03 '15 at 15:54

If you want a query that returns the time portion of the date without having to alter the nls_date_format you can convert the date you got from the to_date function to a text representation (the default text representation is dd-Mon-yy as you found out) like this:

select to_char(
   to_date(A,'DD/MM/YY HH24:MI:SS'),
   ''DD/MM/YY HH24:MI:SS')
from MyTable;

Ideally, column A of MyTable should have been defined as a date column instead of a varchar2 column. There are several reasons to do that; data integrity, and better SQL optimization.

Convert string to date in Oracle SQL

4 Answers4