0

I have a string of timestamp as 27/Mar/2023:15:36:58 +0900

I want to cast it to timezone(0) preserving the timestamp info.

I am trying to run following SQL but it fails saying Invalid Timestamp

CAST('27/Mar/2023:15:36:58 +0900' AS TIMESTAMP(0) WITH TIME ZONE FORMAT 'DD/MMM/Y4:HH:MI:SSBZ')

Is there any way to pass custom timezone hours format to CAST function.

curious_nustian
  • 596
  • 2
  • 7
  • 22
  • 1
    Don't think `+0900` is correct for time zone - you need `+09:00`. +HH:MI – Andrew Apr 14 '23 at 19:48
  • 1
    Localized timestamp literals are of a form `YYYY-MM-DD HH:MI:SSPho:mo` where the P is either a plus sign or minus sign and ho and mo are the hours and minute offset from UTC you want to specify. The format is quite rigid and finicky. – Chris Maurer Apr 14 '23 at 19:54
  • Or `to_timestamp_tz(x,'dd/mon/yyyy:hh24:mi:ss tzhtzm')` works, but returns TIMESTAMP(6) WITH TIME ZONE and reducing time/timestamp precision is something else that can be finicky. Probably better to modify the string format and then CAST. – Fred Apr 14 '23 at 22:27
  • @Fred `To_Timestamp_Tz` throwing error `unknown character in date` – curious_nustian Apr 16 '23 at 19:06

0 Answers0