topic Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format. in QlikView

SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Fri, 20 Nov 2020 18:02:30 GMT

Hey guys,

I am loading data from Oracle and I get the following error:

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

From what I read in the internet the problem is with the time format in the database.

Since I cannot touch the database itself, how can I load my data, only by fixing my script?

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

simospa — Tue, 13 Oct 2015 14:19:47 GMT

Try to post a snippet of code so we can help you easily 🙂

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

nizamsha — Tue, 13 Oct 2015 14:24:29 GMT

i Hope you are writing increment load comparing Qik view date with Database date if its mean this error will throw is that correct

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Tue, 13 Oct 2015 14:26:39 GMT

Idk if this will help you, it is just a basic load...It works for 10 other tables i am loading, only this and another one are failing !

LOAD

     ID,
     Type,
     Date("DATA_BEG") as "DATA_BEG",
     Date("DATA_END") as "DATA_END",
     Vaue,
     ID2,
     ID3,

ID4,
STATUS;

SQL SELECT *

FROM TableX;

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

nizamsha — Tue, 13 Oct 2015 14:27:11 GMT

Hi,

IF You normally load the data from database u wont get any issue but comparing it will create a issue ,when i was facing the problem i used to do two things

1) i will change the date format in Qlikview as like your Database format

2) from Database itself i will change the date in what format i want

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Tue, 13 Oct 2015 14:28:17 GMT

I am sorry, but I do not understand. What exactly do you mean ?

Thank you for your help

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

nizamsha — Tue, 13 Oct 2015 14:29:33 GMT

Hi,

Why you r Passing double quotation in Date Field

Try this

LOAD

     ID,
     Type,
     Date(DATA_BEG,'DD-MM-YYYY') as DATA_BEG,
     Date(DATA_END,'DD-MM-YYYY') as DATA_END,
     Vaue,
     ID2,
     ID3,

ID4,
STATUS ;

SQL SELECT *

FROM TableX;

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Tue, 13 Oct 2015 14:31:45 GMT

Ok, so if I got you right:

1) means to change the "SET TimeFormat= ...", "SET DateFormat=...." and "SET TimestampFormat=..."

to the ones that are in the Oracle db? ...I will try that and see what happens.

2) I cannot touch anything in the DB

Furthermore, I tried to comment the 2 date fields, but the error is the same ...

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Tue, 13 Oct 2015 14:36:35 GMT

I tried all kinds of variations of what you imply above, but nothing worked ...same error !

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Clever_Anjos — Tue, 13 Oct 2015 14:41:25 GMT

Do you have access to an Oracle Client?

Would you mind posting the result of

SELECT *

FROM TableX;

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

nizamsha — Tue, 13 Oct 2015 14:47:26 GMT

Did u got or not what u want

try this if still you are not getting

comment the two date field and check weather its working it should work

even that u r not getting again load the data from the database and check what is the difference between this code vs

your new code

if u got what u want mark this a correct or helpful so it will be helpful to others

LOAD

     ID,
     Type,
     Date(DATA_BEG,'DD-MM-YYYY') as DATA_BEG,
     Date(DATA_END,'DD-MM-YYYY') as DATA_END,
     Vaue,
     ID2,
     ID3,

ID4,
STATUS;

SQL SELECT *

FROM TableX;

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

nizamsha — Tue, 13 Oct 2015 14:59:00 GMT

As cleveranjos said do u have a access ,try to load only the query and post the error

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Wed, 14 Oct 2015 06:25:52 GMT

Yes, I have access. I load other tables from there (the oracle database). Only some are giving me this error. The error would appear in the beginning of the load or it could appear after some millions of records are loaded. I also tried all that Nizam HM suggested, but to no avail. Still doesn't work.

Re: SqlState: 22007, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Invalid datetime format.

Clever_Anjos — Wed, 14 Oct 2015 11:21:42 GMT

If is it possible:

Dump your query results to a text file
Try to load this file into QlikView

If generates the error, the trace screen will show nearly the row line that is generating that error