Adding Date Time format expression in BAQ

Happy September fellow Epicor peeps!
After almost 20 years in working in development and implementation with Epicor products I was presented with a question I have never thought about on a BAQ expression I wrote involving a Date Time field.
The End User requested that I set a second date field to be exactly 365 days from the date in the first date field, as an Expiration Date. When I set the date to 1/1/2020 12:00am, I noticed that the 365 day interval did not take me to 1/1/2021… it took me only to 12/31/2020 12:00am. My wife pointed out that technically 1/1/2021 would be 365 days, 23 hours, 59 minutes, and 59 seconds (She’s an accountant, many will understand :slight_smile: )
The end user is in the Aerospace and Defense Industry so the dates and times have to be dead on. Would it be better if I set the Time Interval to 1 Year, or 366 days to get a full 365?

Thanks for your feedback,

What you did was correct. 2020 is a leap year which has 366 days.

Now had the user requested it be “one year”, you’d have some choices.

A. Add 365 like you did, and lose a day every 4 years.

B. Add 365.25, and be off by some rounding, but not lose a day every 4 years.

C. Add 1 to the year. 1/1/2020 → 1/1/2021, etc… But what to do if the input date is 2/29/2020? There is no 2/29/2021.

The datetime interval of “one month” is even more problematic.

P.S. - your Rx insurance goes with A. You get “a month”, but actually only get 30 days. After 12 months, you’ve only received 360. Not the 365 that 12 consecutive months actually is.

1 Like

I’m not following that Accountant Math. Why would it be 1 second shy of 366 days?

1 Like

I would guess that is the expiration limit after that would be the correct expiration for this leap year. I also follow your suggestions and I like the simple +1 to the year, and for the leap year 2/29 if that’s expiration date I would think 2/28 to be within cutoff date and 3/1 to be an expiration or past due.

1 Like

just a thought.
sure you could identify if the current year is leap year or not, if leap year add 366 otherwise add 365, validate the resulting date with isdate function and reverse engineer the date difference is a year between the resulting date and current date.

1 Like

This is what Excel and T-SQL will do:

1 Like

I think it would come down to MS wouldn’t it :slight_smile:

Option C is what I usually go with. You correctly pointed out the issue we had with that method. Before I filtered out previously expired dates, we were reviewing the data results and there was one entry in the rows that looked odd because the dates did not align like I expected them to. It was from year 2016.

So it really comes down to what the user wants - which isn’t always the same as what they asked for.

And when vague terms are used - like “a year” - they need to be clarified.

1 Like