How to use the DateAdd() Function to Get the First of the Year
The DateAdd() function shows up in some Epicor® reporting tools but in this blog post I’ll be focusing on using it in a BAQ’s (Business Activity Query) criteria. This function is useful any time you are trying to calculate a date based on another date. Which helpful for us because we are trying to find the first of the year based on today’s date. Today being the day the report/dashboard is ran. This can be useful when calculating anything you want for the current year or really any timeframe.
In this example, I’m using the InvcHead table which has the Invoice Date field in it. In the end, I’ll be able to see all of the invoices that occur in the current year only. You can see in the screen shot below the Invoice Date was selected in the Table Criteria tab with a greater than or equal to comparator selected. Specified expression is selected in the Filter Value drop down because we need to write and expression using the DateAdd() function to get the first of the year value.
To accomplish getting the first of the year we need to build a nested AddDate() function. This means we need to do a AddDate() inside of an AddDate() expression. We do this because the first AddDate () function gets you the first month of the year, then the second AddDate() gets the first day of the month. With those combined, we get the first day of the first month of the year. First, let us look at what each section of the nested AddDate() is doing.
Example 1 the first month of the year:
DateAdd(“M”,- (Month(Constants.Today) -1), Constants.Today)
Breaking the expression down we see an “M” in the first argument slot, this lets DateAdd() know we are using the unit of Months in our calculation. Next is the argument that tells it how many months to subtract. The dash at the beginning of argument two indicates this is a subtraction calculation; omit this if you are adding a value. In the second argument we are saying give us the Month of the current date minus one. The third and final argument is telling DateAdd what to subtract from. In this case it is the constant for Today.
Example 2 the first day of the month:
DateAdd(“d”,- (Day(Constants.Today) -1), Constants.Today)
In Example 2 we are getting the first day of the month, the premise is the same as the above only we are now looking at Days. The first argument in the DateAdd function has a “d” which indicates we are dealing with days. The second argument is subtracting because of the dash at the beginning of the argument. The Day() function gets the day of the month, which is then subtracted by one. The third argument is what is being subtracted from, this is example it is the constant for today.
Nesting the DateAdd() Functions
Now that we have an understanding of what we are accomplishing using each DateAdd() function it is time to nest them together.
Example 3 nesting DateAdd() functions to get first of the year:
DateAdd(“M”,- (Month(Constants.Today) -1),DateAdd(“d”,- (Day(Constants.Today) -1), Constants.Today) )
The basic idea here is to substitute the ‘being subtracted from’ value with a DateAdd() expression. So if Example one gets the first month of the year and Example two gets the first day of the month; combing those will get the first day of the first month.
Here we can see the output for each Example if it was ran on 11/29/2016.
Epicor® SSRS, Epicor® Development, Epicor® 10, Epicor® 9, Epicor® System administration, Epicor® Dashboards