?
Solved

Converting string to date in Oracle

Posted on 2010-11-22
17
Medium Priority
?
814 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 1600 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 400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

719 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