data services query does not pull data using required date.

[former_member505874]

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.

[werner_daehn · Accepted solution]

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.

[former_member505874 · Accepted solution]

Thank you so much.