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.

DECLARE @testdate date

SET @testdate = '1975-01-26'

SELECT

-- Get the number of calendar quarters since 0

        DATEDIFF(qq,0,@testdate) AS Step1

-- Add 1 quarter

      , DATEDIFF(qq,0,@testdate)+1 AS Step2

-- Add the number of quarters in Step2 above to 0 to get the earliest date of next quarter

      , DATEADD(qq, DATEDIFF(qq,0,@testdate)+1,0) AS Step3

-- Subtract 3 milliseconds to get to the last datetime value of current quarter

      , dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@testdate)+1,0)) AS Step4

-- Since i was working in SQL 2008, which supports separate data types for DATE and TIME, 

-- I converted the result in Step4 to DATE

      , CONVERT(date,dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@testdate)+1,0))) AS QuarterEndDate

The query results are as follows:

Step1       Step2       Step3                   Step4                   QuarterEndDate

----------- ----------- ----------------------- ----------------------- --------------

300         301         1975-04-01 00:00:00.000 1975-03-31 23:59:59.997 1975-03-31

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.

-- Let's create a tiny table

CREATE TABLE Smurf

(

        Smurf varchar(10)

      , HeightInApples dec(2,1)

)

-- Populate the table with a small number of records

INSERT INTO Smurf VALUES('Brainy',3.1)

INSERT INTO Smurf VALUES('Hefty',3.5)

INSERT INTO Smurf VALUES('Painter', 3.7)

-- Watch these smurfy functions do their thing

SELECT

        Smurf

      , HeightInApples

      , FLOOR(HeightInApples) AS FloorValue

      , CEILING(HeightInApples) AS CeilingValue

FROM Smurf

Results:

Smurf      HeightInApples    FloorValue  CeilingValue

---------- ----------------- ----------- -------------

Brainy     3.1               3           4

Hefty      3.5               3           4

Painter    3.7               3           4

-- Cleaning up after yourself is smurftastic

DROP TABLE Smurf

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