http://www.sqlservercentral.com/blogs/briankmcdonald/2010/10/24/sqlbigeek_2700_s-function-friday-_2D00_-return-first-day-of-quarter/

Printed 2014/11/24 06:10PM

SQLBIGeek's Function Friday - Return First Day of Quarter

By Brian K. McDonald, 2010/10/24

 

Brian K. McDonald, MCDBA, MCSD 

Brian K. McDonald

SQLBIGeek

Twitter: @briankmcdonald

 

 

Welcome to the first of my “SQLBIGeek’s Function Friday” blog series. I understand that I am posting it on Sunday for this first post, rather than Friday. However, going forward I am going to post a new function each Friday. Whether it be cleaning up an old function that I have come across (written by others or myself J) or it is just something that I have written lately. Either way, I hope that you enjoy the series and that you can get some useful ideas or functions to use in your environment.

 

 

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

 Execution Plan - Brian K. McDonald

 

** 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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.