Using Date Range in SQL Statement to ODBC Data Source

[mhender]

What SQL statement should I use to return data from table F4111 in a date range of current date to previous year? Creation date column ILCRDJ in table F4111 holds a date entry that is a text string (ie: "06/14/26").  I am using an ODBC server and trying to use SQL statement to return by parameter. Here is an attempt and the error message received.

SELECT *
FROM F4111
WHERE ILCRDJ >= {fn CURDATE()-365}

"ODBC: ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0182 - A date, time, or timestamp expression not valid."

[drew-sk]

hi @mhender -

noting that you're asking a question about DB2 on the SQL database in Fabric forum, but it looks like @v-achippa noticed that it was off topic and provided a response specific to DB2.

[v-achippa · Accepted solution]

Hi @mhender,

Thank you for reaching out to Microsoft Fabric Community.

The error here is likely because ILCRDJ is stored as a text field not as a date datatype, so it cannot be compared directly with CURDATE().

Please convert the value to a date value first before comparing it, or use the same date format that is stored in the ILCRDJ for the comparison.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @mhender,

As we haven’t heard back from you, we wanted to kindly follow up to check if the responseI have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @mhender,

We wanted to kindly follow up to check if the response I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa