Solved

Converting string to date in Oracle

Posted on 2010-11-22
17
744 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Single ERP VS muttiple Application or Systems 6 64
Pfile and SPfile - Oracle 2 57
PAYER_ID has both atributes 4 31
Distinct values from all columns in a table?? PL SQL 4 23
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

16 Experts available now in Live!

Get 1:1 Help Now