• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

Converting string to date in Oracle

I want to convert string "Mon Oct 11 20:23:21 BST 2010" to date
0
gram77
Asked:
gram77
  • 7
  • 6
  • 4
2 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try :

select to_timestamp_tz('Mon Oct 11 20:23:21 BST 2010','Dy Mon dd hh24:mi:ss TZD YYYY')
from dual
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
sdstuberCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gram77Author Commented:
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
 
sdstuberCommented:
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
 
gram77Author Commented:
sdstuber:
update your timezone file -- how do i do that?
0
 
gram77Author Commented:
seems such an upgrade can only be done by a DBA on the database.

As a developer i can't do much..
0
 
sdstuberCommented:
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
 
gram77Author Commented:
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
 
sdstuberCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
gram77Author Commented:
sdstuber, nav_kumar_v:
thanks for pointing that out, that was a typo

Thanks for your help
0
 
gram77Author Commented:
using  oracle  sqldeveloper ver: 2.1.1.64.39
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i don't use sqldeveloper and using only toad & sql*plus.

Wait for sdstuber to respond. Thanks,
0
 
sdstuberCommented:
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
 
gram77Author Commented:
sdstuber:
correct it works in TOAD
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now