Forum Discussion

mhender's avatar
mhender
Frequent Visitor
2 months ago
Solved

SQL statement to return data from current date to year prior range.

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....
Need Help
  • v-moharafi-msft's avatar
    v-moharafi-msft
    2 months ago

    Hi mhender 

    Thank you for reaching out to the Microsoft Fabric Community and Thanks to Ritaf1983  and Shai_Karmani  for providing meaningful insights.

    Based on the error message and the details shared so far, this appears to be related to the underlying Oracle/JD Edwards database rather than Microsoft Fabric itself. Since we primarily support Fabric-related issues in this community, we may not be the best forum to troubleshoot database-specific SQL syntax and ODBC behavior for Oracle/JDE environments.

    I recommend raising a support request through the Oracle Customer Community:

    Oracle Customer Community

    You can also contact Oracle Support using the Support link available in the footer of that page. Their team will be better positioned to assist with JDE-specific date formats, ODBC connectivity, and database function compatibility.

    Thank you for your understanding.


    Best Regards,
    Abdul Rafi

Ritaf1983's avatar
Ritaf1983
Super User
2 months ago

Hi mhender 

You probably do not need to convert ILCRDJ to a regular date in the WHERE clause. In JDE, this field is typically stored as a Julian date in CYYDDD format, not as a normal text date. A better approach is to convert the date boundaries — today and one year ago — into JDE Julian format, then compare them to ILCRDJ.

For SQL Server:

SELECT *
FROM F4111
WHERE TRY_CONVERT(int, LTRIM(RTRIM(ILCRDJ))) BETWEEN
(
(YEAR(DATEADD(YEAR, -1, GETDATE())) - 1900) * 1000
+ DATEPART(DAYOFYEAR, DATEADD(YEAR, -1, GETDATE()))
)
AND (
(YEAR(GETDATE()) - 1900) * 1000
+ DATEPART(DAYOFYEAR, GETDATE())
);

This avoids converting every row into a date just to filter it. Just make sure ILCRDJ contains valid JDE Julian values and no blanks or non-numeric strings.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly