How change UTC time to Europe/Helsinki in SQL to make it working via Excel connection

Question

I am working in Oracle server and want to run the following query inside excel workbook

Select current_timestamp, FROM_TZ(CAST(current_timestamp AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki' 
from
  MYDB OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

When I ran SQL in SQL Workbench, everything works perfectly, but when I try to create a connection to Oracle server from Excel and run the same query I have the following error in the workbook

As I understood, my driver does not read FROM_TZ(CAST(..))) function in Excel. How Can I modify my query to start working in Excel?

Driver is Oracle in OraClient11g_home1 (64 bit)

UPDATE:

This error also comes if I use the following functions in Excel:

cast(DATA_DTTM as timestamp) AT TIME ZONE 'UTC' as utc
FROM_TZ(CAST(DATA_DTTM AS TIMESTAMP), 'UTC')
FROM_TZ(CAST(DATA_DTTM AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki'

I want to tell that the certain timestamp column is in UTC format and then convert it to 'Europe/Helsinki', but Excel does not read AT TIME ZONE 'UTC' and FROM_TZ() (I tried to run them in Excel separately) + the same issue with TO_TIMESTAMP_TZ() function Before I used Vertica and this manipulation was done by cast(DATA_DTTM as timestamp) AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Helsinki' as utc

The code I used:

select 
cast(DATA_DTTM as timestamp) AT TIME ZONE 'UTC' as utc
FROM_TZ(CAST(DATA_DTTM AS TIMESTAMP), 'UTC')
FROM_TZ(CAST(DATA_DTTM AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki'
from MYDB

Are there any alternative ways to set timestamp timezone and then convert it to another timezone?

Please [edit] your question with details of the ODBC driver you are using. Also, have you tried taking out bits of the query to find which part the driver is complaining about? — MT0, May 25 '21 at 09:22
I added the name of the driver. The second part I did not understand how to do it — Priit Mets, May 25 '21 at 09:32
Start with `SELECT current_timestamp FROM DUAL` and see if it works in Excel, then `SELECT current_timestamp FROM mydb` then `Select current_timestamp, FROM_TZ(CAST(current_timestamp AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki' from MYDB` then try it with the `FETCH` but without the `OFFSET` then finally try your entire query.... break the problem into smaller and smaller parts and see what the smallest part is that fails. — MT0, May 25 '21 at 09:45
Which ODBC driver do you use? The Oracle ODBC driver or the Microsoft ODBC driver? (or even something else, see https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999) — Wernfried Domscheit, May 25 '21 at 09:58
`CURRENT_TIMESTAMP` returns a `TIMESTAMP WITH TIME ZONE`. There is no reason to cast it to `TIMESTAMP` (i.e. remove the time zone) and then add the time zone again with `FROM_TZ`. Use simply `current_timestamp at time zone 'Europe/Helsinki'` or even simple `LOCALTIMESTAMP` - if appropriate. — Wernfried Domscheit, May 25 '21 at 10:01
@WernfriedDomscheit There is a reason if `CURRENT_TIMESTAMP` is not in the `UTC` time zone but you would like it treating as if it was. — MT0, May 25 '21 at 10:03
Did you try to remove the `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY` clause? This was introduced in Oracle 12 and may cause the error. `FROM_TZ(...` exists since long time. — Wernfried Domscheit, May 25 '21 at 10:07
@MT0 Version is 11.02.00.01. I tried with `CURRENT_TIMESTAMP` to make the question reproducible. In real cases, I have a variable that is needed to be set as a timestamp in the UTC zone and then converts it to Helsinki time. I also tried `SELECT current_timestamp FROM DUAL`, nd it has the same error in Excel, but as I told I do not use `current_timestamp` in real case — Priit Mets, May 25 '21 at 10:07
@WernfriedDomscheit Yes, if I remove `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`, the same error comes — Priit Mets, May 25 '21 at 10:10

Wernfried Domscheit · Answer 1 · 2021-05-26T14:42:56.620

In deed, looks like the Oracle ODBC driver does not support TIMESTAMP WITH TIME ZONE data type. See Using the Oracle ODBC Driver.

You need to cast the value to a plain TIMESTAMP value. And you must use column alias. This code works for me:

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

   con.Open "DRIVER={Oracle in OraClient12Home1_32bit};SERVER=dbname;UID=MYUSER;PWD=***secret***;DBQ=dbname"
   cmd.ActiveConnection = con
   cmd.CommandType = adCmdText
   
   cmd.CommandText = _
      "SELECT CAST(CURRENT_TIMESTAMP AS TIMESTAMP) as col_1, " & _
          "CAST(FROM_TZ(CAST(CURRENT_TIMESTAMP AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Helsinki' AS TIMESTAMP) as col_2 " & _
      "from MYDB " & _
      "OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY"
   
   Set rs.Source = cmd
   rs.CursorType = adOpenStatic
   rs.Open
   
   Do Until rs.EOF
      Debug.Print rs(0).Value, rs(1).Value
      rs.MoveNext
   Loop
   rs.Close
   con.Close

Another solution is to use the Oracle OLE DB provider instead of ODBC. The connection string would be

con.Open "Provider=OraOLEDB.Oracle;Data Source=dbname;User Id=MYUSER;Password=***secret***"

All others would remain the same.

The OLE DB provider supports TIMESTAMP WITH TIME ZONE, however it makes an implicit cast to TIMESTAMP, i.e. you will not get any time zone information. See TIMESTAMP Data Types

Thank you for your response. I just checked and I am using `MSDASQL.1` provider will try to change it, but do you know, are there any other driver that I can use in Oracle? — Priit Mets, May 28 '21 at 10:39
I never used `MSDASQL` it may not work anymore, see [MSDASQL and Oracle ODBC](https://en.wikipedia.org/wiki/Microsoft_Data_Access_Components#MSDASQL_and_Oracle_ODBC) For Oracle I know all these: https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999 — Wernfried Domscheit, May 28 '21 at 12:34
Looks like provider `MSDASQL` is an OleDB driver which access an ODBC database. You would add another layer between your Oracle and your Excel, I don't see any reason to use it. — Wernfried Domscheit, May 28 '21 at 12:44

How change UTC time to Europe/Helsinki in SQL to make it working via Excel connection

1 Answers1