Why does oracle sql/dapper not recognize these numbers?

100 Views Asked by At

Writing a custom Oracle SQL query in .NET using Dapper for dynamic parameters. The field that is giving me an issue is a number(6, 0) field that represents a date field in JD Edwards (JDE Julian Date). In my C# application I have a utility function that converts a date time to JDE Julian Date. Here is the Where clause of the query:

WHERE WR.WLOPST IN :OpStatus AND
    WO.WASRST IN :WoStatus AND
    TRIM(WL.MCRP01) IN :Plants AND WC.IWMCUW IN :Depts AND WR.WLMCU IN :WorkCenters
    AND WR.WLDRQJ >= :DateStart AND WR.WLDRQJ <= :DateEnd AND WT.MEY55OHOLD IN :Holds  
    AND LENGTH(REPLACE(WR.WLAPID, '            ', ' ')) >= :AssetAssigned AND  WR.WLDOCO >= :WorkOrderLow AND  WR.WLDOCO <= :WorkOrderHigh
    AND WO.WAVR01 >= :PESNumberLow AND WO.WAVR01 <= :PESNumberHigh AND RT.P1Y55OP IN :OpCodes
ORDER BY WR.WLDOCO"

I've narrowed down the issue in the where clause to WR.WLDRQJ <= :DateEnd. What is weird is when the date is 02/15/2024 (124046) this query runs fine. When I choose any time past that like 02/16/2024 (124047) I get ORA-01722: invalid number. I validate every datetime value to its Julian Value to make sure the conversion is correct. Furthermore, I can run this in SQL Developer just fine which leads me to believe this may be a dapper issue with dynamic parameters. When debugging I can see what is being assigned to the parameter:

Debug of dynamic parameter

            if (queryParams.StartDate != null || queryParams.EndDate != null)
            {
                dateStart = JdeUtility.ConvertDateToJulian(queryParams.StartDate);
                dateEnd = JdeUtility.ConvertDateToJulian(queryParams.EndDate);
            } else
            {
                dateStart = 90001;
                dateEnd = 125268;
            }

            parameters = new Dapper.DynamicParameters();
            parameters.Add(":OpStatus", OpStatus);
            parameters.Add(":WoStatus", WOStatus);
            parameters.Add(":Plants", plants);
            parameters.Add(":Depts", departments);
            parameters.Add(":WorkCenters", workcenters);
            parameters.Add(":DateStart", dateStart);
            parameters.Add(":DateEnd", dateEnd);
            parameters.Add(":OpCodes", queryParams.OpCodes);

DateTime to Julian Date method

        public static int ConvertDateToJulian(DateTime? date)
        {
            if (date.HasValue)
            {
                return ((date.Value.Year - 1900) * 1000) + date.Value.DayOfYear;
            }

            return 0;
        }

SQL Developer enter image description here

Dapper.Logging Output:

WHERE WR.WLOPST IN (:OpStatus1,:OpStatus2,:OpStatus3,:OpStatus4,:OpStatus5,:OpStatus6,:OpStatus7,:OpStatus8,:OpStatus9,:OpStatus10,:OpStatus11,:OpStatus12,:OpStatus13,:OpStatus14,:OpStatus15,:OpStatus16) AND
    WO.WASRST IN (:WoStatus1,:WoStatus2,:WoStatus3,:WoStatus4,:WoStatus5,:WoStatus6,:WoStatus7,:WoStatus8,:WoStatus9,:WoStatus10,:WoStatus11,:WoStatus12,:WoStatus13,:WoStatus14,:WoStatus15,:WoStatus16,:WoStatus17,:WoStatus18,:WoStatus19,:WoStatus20,:WoStatus21,:WoStatus22,:WoStatus23,:WoStatus24,:WoStatus25,:WoStatus26,:WoStatus27,:WoStatus28,:WoStatus29,:WoStatus30,:WoStatus31,:WoStatus32,:WoStatus33,:WoStatus34,:WoStatus35,:WoStatus36,:WoStatus37,:WoStatus38,:WoStatus39,:WoStatus40,:WoStatus41,:WoStatus42,:WoStatus43,:WoStatus44,:WoStatus45,:WoStatus46,:WoStatus47,:WoStatus48,:WoStatus49,:WoStatus50,:WoStatus51,:WoStatus52,:WoStatus53,:WoStatus54) AND
    TRIM(WL.MCRP01) IN (:Plants1) AND WC.IWMCUW IN (:Depts1) AND WR.WLMCU IN (:WorkCenters1,:WorkCenters2,:WorkCenters3,:WorkCenters4,:WorkCenters5,:WorkCenters6,:WorkCenters7,:WorkCenters8,:WorkCenters9,:WorkCenters10,:WorkCenters11,:WorkCenters12,:WorkCenters13,:WorkCenters14,:WorkCenters15,:WorkCenters16)
    AND WR.WLDRQJ >= :DateStart AND WR.WLDRQJ <= :DateEnd AND WT.MEY55OHOLD IN (:Holds1,:Holds2)  
    AND LENGTH(REPLACE(WR.WLAPID, '            ', ' ')) >= :AssetAssigned AND  WR.WLDOCO >= :WorkOrderLow AND  WR.WLDOCO <= :WorkOrderHigh
    AND WO.WAVR01 >= :PESNumberLow AND WO.WAVR01 <= :PESNumberHigh AND RT.P1Y55OP IN (:OpCodes1,:OpCodes2,:OpCodes3,:OpCodes4,:OpCodes5,:OpCodes6,:OpCodes7,:OpCodes8,:OpCodes9,:OpCodes10,:OpCodes11,:OpCodes12,:OpCodes13,:OpCodes14,:OpCodes15,:OpCodes16,:OpCodes17,:OpCodes18,:OpCodes19,:OpCodes20,:OpCodes21,:OpCodes22,:OpCodes23,:OpCodes24,:OpCodes25,:OpCodes26,:OpCodes27,:OpCodes28,:OpCodes29,:OpCodes30,:OpCodes31,:OpCodes32,:OpCodes33,:OpCodes34,:OpCodes35,:OpCodes36,:OpCodes37,:OpCodes38,:OpCodes39,:OpCodes40,:OpCodes41,:OpCodes42,:OpCodes43,:OpCodes44,:OpCodes45,:OpCodes46,:OpCodes47,:OpCodes48,:OpCodes49,:OpCodes50,:OpCodes51,:OpCodes52,:OpCodes53,:OpCodes54,:OpCodes55,:OpCodes56,:OpCodes57,:OpCodes58,:OpCodes59,:OpCodes60,:OpCodes61,:OpCodes62,:OpCodes63,:OpCodes64,:OpCodes65,:OpCodes66,:OpCodes67,:OpCodes68,:OpCodes69,:OpCodes70,:OpCodes71,:OpCodes72,:OpCodes73,:OpCodes74,:OpCodes75,:OpCodes76,:OpCodes77,:OpCodes78,:OpCodes79,:OpCodes80,:OpCodes81,:OpCodes82,:OpCodes83,:OpCodes84,:OpCodes85,:OpCodes86,:OpCodes87,:OpCodes88,:OpCodes89,:OpCodes90,:OpCodes91,:OpCodes92,:OpCodes93,:OpCodes94,:OpCodes95,:OpCodes96,:OpCodes97,:OpCodes98,:OpCodes99,:OpCodes100,:OpCodes101,:OpCodes102,:OpCodes103,:OpCodes104,:OpCodes105,:OpCodes106,:OpCodes107,:OpCodes108,:OpCodes109,:OpCodes110,:OpCodes111,:OpCodes112,:OpCodes113,:OpCodes114,:OpCodes115,:OpCodes116,:OpCodes117,:OpCodes118,:OpCodes119,:OpCodes120,:OpCodes121,:OpCodes122,:OpCodes123,:OpCodes124,:OpCodes125,:OpCodes126,:OpCodes127,:OpCodes128)
ORDER BY WR.WLDOCO
                
                
Parameters: ****other stuff***
 [DateStart, 123228], [DateEnd, 124047], ****other stuff****

I can't figure out why certain dates work with this query and others don't.

0

There are 0 best solutions below