Question Re: data services query does not pull data using required date. in Technology Q&A

data services query does not pull data using required date.

former_member505874 — Wed, 11 Nov 2020 05:35:27 GMT

Hi all

I want to extract data from CKIS table on sap but when I use the "WHERE clause" in data services to extract data using a date which 2020.10.31 I get all data for the whole 2020.Please advice on were I am going wrong .Screen prints below.

Re: data services query does not pull data using required date.

werner_daehn — Wed, 11 Nov 2020 05:49:50 GMT

In short: Watchout for implicit data type conversion warnings!

Detail:

You compare the KADKY field of data type date with a string of value '2020-10-31'. The only option this can be done is by converting KADKY to a string (using the BODS internal date format) and then the value is e.g. '2020.01.01'. The substring '2020.' is always larger than '2020-' no matter what other chars follow.

You want

KADKY >= to_date('2020-10-31', 'YYYY-MM-DD')

or better to stick to the default date format

KADKY >= to_date('2020.10.31', 'YYYY.MM.DD')

Now it is clear that the left and right side is a date and a date comparison will happen instead of a string comparison. Also important for performance.

Re: data services query does not pull data using required date.

former_member505874 — Wed, 11 Nov 2020 06:13:29 GMT

Thank you so much.