Solved

Converting string to date in Oracle

Posted on 2010-11-22
17
804 Views
Last Modified: 2013-12-07
I want to convert string "Mon Oct 11 20:23:21 BST 2010" to date
0
Comment
Question by:gram77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34187265
try :

select to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY')
from dual
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 400 total points
ID: 34187282
if you need only date part of it... i mean if you do not need timezone part of the value, then use the below.

Both the below will work.

SELECT CAST(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') AS DATE) DT FROM DUAL;
 
SELECT TRUNC(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY')) DT FROM DUAL;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 34187291
DATE types don't have timezone's,  only timestamps with timezones do

If you want a DATE type,  use the conversion above as nav_kum_v showed but then CAST it to a date


select cast(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') as date)
from dual;
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:gram77
ID: 34187331
None of the examples work..

select cast(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') as date) from dual;

ORA-01857: not a valid time zone
01857. 00000 -  "not a valid time zone"
*Cause:    
*Action:
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34187352
update your timezone file

I tested the queries above.  Worked fine


returned 2010/10/11 20:23:21


or 2010/10/11 20:23:21.000000000 -05:00 without the cast
0
 

Author Comment

by:gram77
ID: 34187370
sdstuber:
update your timezone file -- how do i do that?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34187378
0
 

Author Comment

by:gram77
ID: 34187426
seems such an upgrade can only be done by a DBA on the database.

As a developer i can't do much..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34187876
yes,  you're right, your dba will need to do the timezone update for you.

alternate approach... you could parse out the TZ section of the string and convert the rest of it to a DATE

TO_DATE(
           SUBSTR('Mon Oct 11 20:23:21 BST 2010', 1, 19)
           || SUBSTR('Mon Oct 11 20:23:21 BST 2010', -4),
           'Dy Mon dd hh24:mi:ssyyyy')
0
 

Author Comment

by:gram77
ID: 34188657
select cast(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') as date) from dual;
does not work in sql editor,

but as pl/sql editor it works, why?:
v_str:=select cast(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') as date);

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34188833
what is your client version?  in the first, you'll be returning a timestamp with time zone type value

in the second, the pl/sql is all performed server side.  

I'm surprised at your pl/sql example though,  that's not legal syntax.

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34193441
As said by sdstuber,

v_str:=select cast(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') as date);  --> this is incorrect

it has to be the below right ?

v_str:=cast(to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY') as date);



0
 

Author Comment

by:gram77
ID: 34193699
sdstuber, nav_kumar_v:
thanks for pointing that out, that was a typo

Thanks for your help
0
 

Author Comment

by:gram77
ID: 34193707
using  oracle  sqldeveloper ver: 2.1.1.64.39
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34193730
i don't use sqldeveloper and using only toad & sql*plus.

Wait for sdstuber to respond. Thanks,
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34193893
I've confirmed the problem with sqldeveloper.  I also checked if there was anything special with my client and it made no difference.  9i or 11g client. Both work in sqlplus and toad but not in sqldeveloper

So, I think I have to assume it's something in the jdbc communication of sqldeveloper.  OCI works fine.
0
 

Author Comment

by:gram77
ID: 34195885
sdstuber:
correct it works in TOAD
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question