Solved

Converting string to date in Oracle

Posted on 2010-11-22
17
737 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
  • 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 73

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
 

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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now