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

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.

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

Thank you v-moharafi-msft, Shai_Karmani  and Ritaf1983 for your insights and consideration with this topic.