0

i have to convert different timezone string to date. in my table, i have column defined as varchar2. which contains data as below. i need to convert to one time zone(exampple to central time) and extract date from that. i tried different ways but getting invalid month error or not date. ANy suggestions from your end?

1/16/2020 6:28:44 AM -08:00
11/8/2019 3:20:30 AM -05:00
10/25/2019 6:08:21 PM +05:30
srinath
  • 91
  • 1
  • 8

1 Answers1

2

I believe you first need to convert it to timestamp with timezone ad then cast it to date:

select CAST(to_timestamp_tz (dat_col, 'MM/DD/YYYY HH12:MI:SS AM TZH:TZM' ) AS DATE) 
from test

here is a demo

This will turn your data into specific timezone : 

select 
to_timestamp_tz(dat_col, 'MM/DD/YYYY HH12:MI:SS AM TZH:TZM')at time zone 'Europe/Moscow'
from test

Then you can cast that :

select 
CAST
(to_timestamp_tz(dat_col, 'MM/DD/YYYY HH12:MI:SS AM TZH:TZM')at time zone 'Europe/Moscow'
AS DATE) 
from test

Please provide your expected results for more accurate code...

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • no. you are extracting only date part. i need to convert all timezones to one timezone. then cast date from it. for example, pst 11 pm and est 1am both will have same date. but when u take date its different. – srinath May 29 '20 at 09:17
  • 1
    Hi @srinath Your question was not clear. That is why WilliamRobertson asked you what is that you need and that is why you always need to give expecxted results. Also a code you have tried would be a great thing in the question too.. – VBoka May 29 '20 at 09:18
  • updated query works fine. Thank you. i marked it correct/answered. Thanks alot. – srinath May 29 '20 at 10:29
  • Hi @srinath, I am happy to help. You are welcome. Cheers! – VBoka Jun 01 '20 at 05:22