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

Shai_Karmani's avatar
Shai_Karmani
Super User
2 months ago

I think this type of question belongs to the DataBase forum
but I'll try to answer: 

ILCRDJ isn't really text, it's Julian date (CYYDDD format). That's why the string conversion keeps fighting you.

The format is: C = century (1 for 2000s), YY = year, DDD = day of year. So today is 126162.

Easiest path is to convert your date boundaries to Julian and compare as numbers, since the column is numeric anyway:

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

The - 1900 gives you the century+year part (126), * 1000 shifts it, then you add day of year.

Two things to watch:

ILCRDJ can hold 0 for blank dates, so maybe add AND ILCRDJ > 0
This is SQL Server syntax. JDE also runs on Oracle and DB2/iSeries and the date functions are totally different there.

If that was helpful, please give a thumbs up and mark it as the accepted solution.

 

Thanks,
Shai Karmani

 

Let's connect in LinkedIn




mhender's avatar
mhender
Frequent Visitor
2 months ago

OK thank you for the explanation on this issue.  I'm thinking I have left out a key factor in this scenario.  Shai, you have aptly pointed out the missing key.  I am using an ODBC server and trying to use SQL statement to return by parameter.  I have placed your solution in the SQL statement box and received "GETDATE in *LIBL type *N not found." error.  Is this an ODBC to SQL bonk?