I was tagged by Jason Strate for cool blog chain. I guess I’m it now.
Get the Last Date in a Calendar Quarter
I needed to accurately calculate the end dates for the four most recently completed calendar quarters prior to the date provided. I found this snippet online and thought it was a really slick way to accomplish finding the quarter end date. I tried to find the site again so i could give props to the person who posted it but cannot. So, my clever friend, I salute thee.
Let’s start by declaring our date variable and setting it to a fine day in history. My parents told me this was my birthday. I don’t really remember but I will give them the benefit of the doubt.
I have dissected the expression below.
The query results are as follows:
So, with this nifty expression, we have determined the end date of the quarter for my birthday (no gifts necessary).
We can also change the number of quarters added (or even subtracted) in Step2 to get the quarter end date for future or past quarters, too.
Make room for FLOOR and CEILING
On the same report referenced above, I also had to implement a business rule by rounding a value up to the nearest thousand. A colleague suggested the CEILING function, which returns the smallest integer that is greater than or equal to the provided numeric expression. As it turn out, the FLOOR function does the exact opposite, returning the largest integer that is less than or equal to the numeric expression.
Let’s look at both functions in action.
Notice that all values were rounded down by the FLOOR function and up by the CEILING function.
For my report, I divided my input values by 1000, ran them through CEILING, and the multiplied by 1000. This rounded my values up to the nearest thousand. I just figured the example above would be a little more fun.
I am tagging Dan English