CODEWITHSUNDEEP
sqloracle-databaseto-date
Omri
Omri

Reputation: 1646

Convert string to date in Oracle SQL

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?

Upvotes: 1

Views: 19243

Answers (4)

Mark Stewart
Mark Stewart

Reputation: 2098

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.

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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.

Upvotes: 1

Onur Cete
Onur Cete

Reputation: 273

You can use timestamp 

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

Upvotes: 0

Related Questions