Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asmitagg
Partner - Contributor III
Partner - Contributor III

ERROR: QVX PIPE ERROR :Not a legal OleAut date.

Hi All,

I m trying to fetch records using below query:

  1. SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT EQ "00171231”;


but I m facing issue while executing this query  with error message as "Not a legal OleAut date."

Using SAPSqlConnector.

Also attaching image of error and log specifying error.

Please suggest.

Thanks & Regards,

Asmita


1 Solution

Accepted Solutions
asmitagg
Partner - Contributor III
Partner - Contributor III
Author

Hi Tamil,

I have tried below steps coz only one date was creating issue:

1. Fetch all the rows except that one record/row values.

LOAD *;

SQL SELECT COLUMN1 COLUMN2 ZFBDT FROM BSAK WHERE ZFBDT != "00171231”;


2.

Then fetch the same table again except " ZFBDT" date field and hard-code the date value in script like below

  Ex:  LOAD *,'00171231' as ZFBDT;

   SQL SELECT COLUMN1 COLUMN2 FROM BSAK WHERE ZFBDT = "00171231”;

   - Then concatenate both the tables.


This is temporary solution to get the whole record for this date.

The main reason for such cases is SAP connector version.

SAP connector 6.1 to 6.3 doesn't support these date.

For this to resolve we have to upgrade connector to 6.3.1 or next.

View solution in original post

18 Replies
wdchristensen
Specialist
Specialist

SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT EQ TO_DATE('00171231’, 'YYYYMMDD')

wdchristensen
Specialist
Specialist

It could also be that you are not using match quotes. The double quotes are not of the same type. Applications like Microsoft Word have different types of quotes. 

asmitagg
Partner - Contributor III
Partner - Contributor III
Author

HI William,

I tried the above solution/query but it's not working.

Its giving "Custom Read Failed error".

Is there any way to convert it in Character format.

Because I m able to fetch other dates like. 0215-05-27 & 5015-04-26 without converting it to date format.

Please suggest.

wdchristensen
Specialist
Specialist

I believe the regional setting controls what date format is acceptable for implicit conversions. Since you already know that MMMM-MM-DD works, I would recommend reformatted your data to fit that pattern. I provided an example on how you can do that below. Good luck!  

substringExample.PNGSELECT ZFBDT FROM BSAK

WHERE ZFBDT EQ CONCAT(SUBSTRING( '00171231', 1, 4 ), '-', SUBSTRING( '00171231', 5, 2 ), '-', SUBSTRING( '00171231', 7, 2 ))

OR Maybe

SELECT ZFBDT FROM BSAK

WHERE ZFBDT EQ CONCAT(SUBSTRING( "00171231", 1, 4 ), "-", SUBSTRING( "00171231", 5, 2 ), "-", SUBSTRING( "00171231", 7, 2 ))

wdchristensen
Specialist
Specialist

I had mismatched quotes in the solution you stated did not work so you might also try:

SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT EQ TO_DATE('00171231', 'YYYYMMDD')


SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT EQ TO_DATE("00171231", "YYYYMMDD")

asmitagg
Partner - Contributor III
Partner - Contributor III
Author

Sorry ,William but both the solutions are not  working. Tried with quotes " " as well as ' ' .

tamilarasu
Champion
Champion

Hi Asmita,

Try,


SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT = '00171231';


If this is not working, please let us know the date format of ZFBDT field.

asmitagg
Partner - Contributor III
Partner - Contributor III
Author

Hi TN,

I have already tried this one, but its not working.

Date format of this field is YYYYMMDD

tamilarasu
Champion
Champion

Asmita,

Can you try this one?

SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT = '20171231';

If this is not working, try full reload and show us the date format snap shot.