Qlik Sense Date Format Issue_ORACLE

[carloswilly]

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

[stevedark]

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

[carloswilly]

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,

[carloswilly]

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

[carloswilly]

Attached the excel with format after substr for reference

[stevedark · Accepted solution]

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

[carloswilly]

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

Carlos