I recently came across a function that returned the first day of the quarter based on a date value supplied. At first glance, it appeared to be a little longer than it needed to be. I could definitely see where they were going with it and why they did it that way, but I thought that I might be able to shrink it down a little bit with less typing (minus the commenting of course). Script 1 below shows us this function.

Script 1: Original Function

CREATE FUNCTION [dbo].[ufn_GetTheFirstDayOfTheQuarter]

(

      @InDate DATETIME

)

RETURNS DATETIME

BEGIN

    DECLARE @OutDate DATETIME

    SET @OutDate =

            CAST(YEAR(@InDate) AS VARCHAR(4)) +

            CASE

                WHEN MONTH(@InDate) IN (1,2,3) THEN '/01/01'

                WHEN MONTH(@InDate) IN (4,5,6) THEN '/04/01'

                WHEN MONTH(@InDate) IN (7,8,9) THEN '/07/01'

                WHEN MONTH(@InDate) IN (10,11,12) THEN '/10/01'

            END

    RETURN @OutDate

END

--Execute it a few times

SELECT dbo.ufn_GetTheFirstDayOfTheQuarter ('3/21/2010')

SELECT dbo.ufn_GetTheFirstDayOfTheQuarter ('6/21/2010')

SELECT dbo.ufn_GetTheFirstDayOfTheQuarter ('9/21/2010')

SELECT dbo.ufn_GetTheFirstDayOfTheQuarter ('12/21/2010')

--Clean up my function

DROP FUNCTION dbo.ufn_GetTheFirstDayOfTheQuarter

I went home that night and looked at the query again and thought for a minute… then I started typing. The results are shown in script 2 and 3 below. Using the DATEDIFF and DATEADD functions, I was able to determine the first day of the quarter in one line without a case statement.

Script 2 was a quick test query that I came up with.

Script 2: Determine First Date in Quarter Select Statement

DECLARE @pInputDate SMALLDATETIME = '9/21/2010'

SELECT DATEPART(Quarter,@pInputDate) AS Quarter

      , DATEADD(qq, DATEDIFF(qq,0,@pInputDate), 0) AS FirstDayOfQuarter 

So using my slightly simplified select script above and incorporating it into a function would look something like that shown in script 3.

Script 3: Determine First Date in Quarter Function

CREATE FUNCTION [dbo].[BKM_ufn_GetTheFirstDayOfTheQuarter]

(

      @InDate    DATETIME

/*=========================================================================

Created By:       Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)

Email:            bmcdonald@SQLBIGeek.com

Twitter:          @briankmcdonald

Date:             10/24/2010

Purpose:          Return the first day of the quarter based on date

                  passed into the function

Usage:            SELECT dbo.BKM_ufn_GetTheFirstDayOfTheQuarter ('3/21/2010')

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

Modification History

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

==========================================================================*/

)

RETURNS DATETIME

BEGIN

      DECLARE @OutDate DATETIME

SET @OutDate = DATEADD(qq, DATEDIFF(qq,0,@InDate), 0)

      RETURN @OutDate

END

GO

--Execute it a few times

SELECT dbo.BKM_ufn_GetTheFirstDayOfTheQuarter ('3/21/2010')

SELECT dbo.BKM_ufn_GetTheFirstDayOfTheQuarter ('6/21/2010')

SELECT dbo.BKM_ufn_GetTheFirstDayOfTheQuarter ('9/21/2010')

SELECT dbo.BKM_ufn_GetTheFirstDayOfTheQuarter ('12/21/2010')

--Clean up my function

DROP FUNCTION dbo.BKM_ufn_GetTheFirstDayOfTheQuarter 

I understand that sometimes it takes more typing to make queries perform better, but in this case the query plan is exactly the same. Figure 1 shows the execution plan of executing each of the functions for 3/21/2010.

Figure 1: Execution Plan

** Note: This is not the only way that one could determine the first date of the quarter, but IMHO is the easiest to understand and quite a bit less typing J **

Please be sure to return for the next BISQLGeek's Function Friday, as I will show you how to parse through some crazy integer values that are representative of duration. I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald