Forum Discussion

mhender's avatar
mhender
Frequent Visitor
2 months ago
Solved

Using Date Range in SQL Statement to ODBC Data Source

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."

SQL database
  • v-achippa's avatar
    v-achippa
    2 months ago

    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

4 Replies

  • v-achippa's avatar
    v-achippa
    Community Support
    2 months ago

    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's avatar
      v-achippa
      Community Support
      2 months ago

      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's avatar
        v-achippa
        Community Support
        1 month ago

        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

  • drew-sk's avatar
    drew-sk
    Microsoft Employee
    2 months ago

    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.