Date problem in QV10 working on previous version

[#1]

Hi,

I have a script loading from an oracle table and data load is limited by start date and end date. This script has been (and still is) working perfectly fine on Qlikview 8.20 (yeah I know, old version ). When transferring this to the newer QV10 server, I get "ORA-01861: literal does not match format string" error. It is the very same script, and I can cope it from the newer server to the old one, and the script works perfect.

Any ideas what I can do to solve this?

Let StartDate = date('2010-01-01','YYYY-MM-DD');
Let EndDate = date(today(),'YYYY-MM-DD');

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE

WHERE("TRANSACTION_DATE">= '$(StartDate)'
AND "TRANSACTION_DATE"< '$(EndDate)' );

BR

Robert

[Miguel_Angel_Baeyens]

Hi Robert,

My guess is that $(StartDate) is not being parsed properly. Add this line just below the LET lines, just to see if the date has the proper format

TRACE $(StartDate);
TRACE $(EndDate);

Let us know the result. You might need to change your date format. If the driver to connect to the Oracle database has not changed, is the only problem I'd see.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Hi and thank you for your input.

The reslut looks like this:

Connected
2010-01-01
2011-10-25
--- Script Finished --- 

I could also mention that I have tried several formats of StartDate and EndDate, for example 'YYYYMMDD', 'YYYY-MM-DD hh:mm:ss' etc etc. I have also tried Timestap instead of Date function.

When loading a few records from the table without any limitation (commenting the where clause), the TRANSACTION_DATE fied gives following format

2006-12-10 12:30:34.

Is there any way in Qlikview checking whether the field is date or varchar or similar, to find out if it could have a leading space or so? This is just a long shot, I have basically no idea how to solve this.

BR

Robert

[#4]

hi try with date#(...) for both variables

[#5]

Hi,

I've tried date#() with several formats as well, still the same error message

[#6]

try this

Let StartDate = date('2010-01-01','YYYY-MM-DD');
Let EndDate = date(today(),'YYYY-MM-DD');

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE

WHERE("TRANSACTION_DATE">= '(StartDate)'
AND "TRANSACTION_DATE"< '(EndDate)' );

[#7]

Hi,

the script now ended in "ORA-01858: a non-numeric character was found where a numeric was expected".

BR

Robert

[#8]

what is the TRANSACTION_DATE format?

[#9]

Hi,

I can not access PL/SQL at the moment, and thus not check the data type of the field TRANSACTION_DATE right now. As mentioned above, if I load some records from the table without any limitation (commented where clause), I get the format of

2006-12-10 12:30:34

But this is just the Qlikview presented format. Is there any way to check the data type of the field via Qlikview?

BR

Robert

[#10]

try this

tmpLoad:
LOAD
makedate(subfield(TRANSACTION_DATE,'-',1),subfield(TRANSACTION_DATE,'-',2),subfield(TRANSACTION_DATE,'-',3)) as TRANSACTION_DATE

WHERE

makedate(subfield(TRANSACTION_DATE,'-',1),subfield(TRANSACTION_DATE,'-',2),subfield(TRANSACTION_DATE,'-',3))>= makedate(2010,01,01)
AND

makedate(subfield(TRANSACTION_DATE,'-',1),subfield(TRANSACTION_DATE,'-',2),subfield(TRANSACTION_DATE,'-',3))< today();

SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE;