topic Re: Qlik Sense Date Format Issue_ORACLE in Connectivity & Data Prep

Qlik Sense Date Format Issue_ORACLE

carloswilly — Thu, 07 Jan 2021 16:24:43 GMT

Hello Community,

Happy New Year!. I am new to qlik development and ran into a strange issue. I have researched on the internet but unfortunately cannot make it work. If you get a chance, please help.

I am trying to load data from Siebel/Oracle db into qlik. When I try to load the data into qlik I am getting “Error: QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format” error.

I commented out all the fields and figured out one date filed is causing the issue

Ran the query against the db using querying tool and it runs fine. Exported the data to a.csv file and was able to load in to qlik without issues. I am not sure what’s going wrong with qlik. I really appreciate any help on this.

Thank you,

Carlos

Re: Qlik Sense Date Format Issue_ORACLE

stevedark — Thu, 07 Jan 2021 17:28:54 GMT

Hi @carloswilly

The error message looks to me like it is being generated by Oracle, rather than Qlik, and I'm therefore surprised that it works okay when copied into Oracle.

Do you have a preceding load on the query, or is it just a straight select from Oracle?

Can you try casting the field to a string in the SQL statement, a preceding load will then be able to convert that string back to a date on the Qlik side.

Is there any comparison or where statements in the Oracle query?

Steve

Re: Qlik Sense Date Format Issue_ORACLE

carloswilly — Thu, 07 Jan 2021 18:00:39 GMT

Hi Steve,

Thank you very much for the response. I tried with both straight select from oracle and preceding load as well but getting the same error message.

There is no comparison.

I will try using the cast function and see if it's works. Thank you

Carlos,

Re: Qlik Sense Date Format Issue_ORACLE

carloswilly — Fri, 08 Jan 2021 16:53:36 GMT

Thank you, Steve. I tried using CAST function but whatever syntax I tried is not working. Maybe I am not doing it right in oracle. I tried “Substr” it’s working but not getting the expected format. I would appreciate if you could point/help me with format

Syntax used for CAST: (tried multiple other variants)
CAST(Formated Time as Date )

Substr syntax:

Substr(Formated Time,1,20) as Formated Time

Format after Substr:

01-APR-16

Expected format:

01/04/2016

Thank you,

Carlos

Re: Qlik Sense Date Format Issue_ORACLE

carloswilly — Fri, 08 Jan 2021 16:55:45 GMT

Attached the excel with format after substr for reference

Re: Qlik Sense Date Format Issue_ORACLE

stevedark — Fri, 08 Jan 2021 23:00:02 GMT

Hi @carloswilly

I'm surprised by the Oracle fieldname Formatted Time, as I wouldn't expect a space to be allowed. Have you changed the field name for the post?

With the cast, you don't want to cast to Date, rather cast to a string which can then be dealt with in Qlik code.

It doesn't matter what format you get back from Oracle as a string, as Qlik can fix it up in the preceding load. For example, if you have the format you show as being after the substr you can do this:

Date(Date#([Formatted Time], 'DD-MMM-YY'), 'DD/MM/YYYY') as [Formatted Time],

The Date# function turns a string into a number (or a Dual to be more precise) and then the Date function formats that number how you want to see it.

The substr function causes you code to work as it will do an implicit cast to string, as you are doing a string function on a date field.

Hope that makes sense?

Steve

Re: Qlik Sense Date Format Issue_ORACLE

carloswilly — Sat, 09 Jan 2021 18:38:55 GMT

Thank you very much Steve. It worked and makes perfect sense, thank you for explaining how Date % function works.

Carlos