Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I m trying to fetch records using below query:
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
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.
SQL SELECT ZFBDT FROM BSAK WHERE ZFBDT EQ TO_DATE('00171231’, 'YYYYMMDD')
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.
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.
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!
SELECT 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 ))
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")
Sorry ,William but both the solutions are not working. Tried with quotes " " as well as ' ' .
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.
Hi TN,
I have already tried this one, but its not working.
Date format of this field is YYYYMMDD
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.