Brian K. McDonald
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]
DECLARE @OutDate DATETIME
SET @OutDate =
CAST(YEAR(@InDate) AS VARCHAR(4)) +
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'
--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]
Created By: Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)
Purpose: Return the first day of the quarter based on date
passed into the function
Usage: SELECT dbo.BKM_ufn_GetTheFirstDayOfTheQuarter ('3/21/2010')
DECLARE @OutDate DATETIME
SET @OutDate = DATEADD(qq, DATEDIFF(qq,0,@InDate), 0)
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')
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, MCSDBusiness Intelligence Consultant – Pragmatic Works
Email: firstname.lastname@example.org | Blogs: SQLBIGeek | SQLServerCentral | BIDN
Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald
Looking forward to more! Great stuff, B-Mac!
Thanks! Keep your eye out for Friday. :)
Nice work. You've used something here that I haven't used before - the date of 0. Is that always 1900-01-01 or is there some server setting that can be modified to change the base date?