Tip of the Day : Example Uses of the RIGHT String Function
Get First Day of the Quarter Function

Get First Day of the Quarter Function

Aside from monthly reports, it is quite common for companies to request for a report of all transactions beginning the start of the quarter until the current date.  This article illustrates the different ways of getting the first day of the quarter for any given date.

First Variant

Getting the first day of the quarter for any given date is quite easy because there are only 4 dates to choose from depending on the month of the date.  The following user-defined function checks for the month of the input date and based on this month, determines the first day of the quarter.

CREATE FUNCTION [dbo].[ufn_GetFirstDayOfQuarter] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    DECLARE @vOutputDate        DATETIME

    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) +
                       CASE WHEN MONTH(@pInputDate) IN ( 1,  2,  3) THEN '/01/01'
                            WHEN MONTH(@pInputDate) IN ( 4,  5,  6) THEN '/04/01'
                            WHEN MONTH(@pInputDate) IN ( 7,  8,  9) THEN '/07/01'
                            WHEN MONTH(@pInputDate) IN (10, 11, 12) THEN '/10/01'
                       END

    RETURN @vOutputDate

END
GO

The function builds the first day of the month by getting the year of the input date, converting it to VARCHAR so that it may be concatenated with the month and day, then appending the first day of the first month of the quarter.  The date string being built is in the "YYYY/MM/DD" format.  Since the data type of the output is DATETIME data type, there is no need to explicitly convert the date string into DATETIME because SQL Server will implicitly convert it.

Second Variant

Not many SQL Server users are aware that when converting a date string into a DATETIME data type, if the DAY part of the date is not specified, SQL Server will automatically assign the value of 1 for the day.  Given this, if you try to CAST the date string "2005 JAN" to a DATETIME data type with the DAY part not specified, SQL Server will convert this to "2005-01-01".

The following version of the user-defined function makes use of this feature of SQL Server wherein if the DAY part is not specified in a date string, a value of 1 is automatically assigned.

CREATE FUNCTION [dbo].[ufn_GetFirstDayOfQuarter] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    DECLARE @vOutputDate        DATETIME

    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + ' ' +
                       CASE WHEN MONTH(@pInputDate) IN ( 1,  2,  3) THEN 'JAN'
                            WHEN MONTH(@pInputDate) IN ( 4,  5,  6) THEN 'APR'
                            WHEN MONTH(@pInputDate) IN ( 7,  8,  9) THEN 'JUL'
                            WHEN MONTH(@pInputDate) IN (10, 11, 12) THEN 'OCT'
                       END

    RETURN @vOutputDate

END
GO

Third Variant

Here's yet another version of the user-defined function that makes use of DATEPART date function with a parameter of "Q" to get the Quarter of the input date instead of the MONTH function.  This version still makes use of the feature of the missing DAY part and simply concatenates the YEAR and first month of the quarter.

CREATE FUNCTION [dbo].[ufn_GetFirstDayOfQuarter] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    DECLARE @vOutputDate        DATETIME

    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + ' ' +
                       CASE DATEPART(Q, @pInputDate)
                           WHEN 1 THEN 'JAN'
                           WHEN 2 THEN 'APR'
                           WHEN 3 THEN 'JUL'
                           WHEN 4 THEN 'OCT'
                       END

    RETURN @vOutputDate

END
GO

Fourth Variant

I have always loved math and as much as possible I try to make use of formulas when deriving values, especially when it comes to dates.  Given this, the following version of the same user-defined function makes use of a formula to derive the first day of the quarter instead of using a CASE statement.

CREATE FUNCTION [dbo].[ufn_GetFirstDayOfQuarter] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    DECLARE @vOutputDate        DATETIME

    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
                       CAST(DATEPART(Q, @pInputDate) * 3 - 2 AS VARCHAR(2)) + '/01'

    RETURN @vOutputDate

END
GO

This version is basically the same as the ones above with the exception of how it derives the month to use.  The following illustrates the relationship between the quarter and the first month of the quarter:

Quarter = 1 - Month = 1 (January)
Quarter = 2 - Month = 4 (April)
Quarter = 3 - Month = 7 (July)
Quarter = 4 - Month = 10 (October)

This Quarter-to-Month relationship can be expressed using the following formula:

((Quarter - 1) * 3) + 1

This formula can then be simplified as follows, which is what is used in the latest version of the user-defined function:

Quarter * 3 - 2

Usage

Here's an example of how to use this user-defined funcation:

SELECT * FROM [dbo].[Sales]
WHERE [TransactionDate] BETWEEN [dbo].[ufn_GetFirstDayOfQuarter] ( GETDATE() ) AND
GETDATE()

This statement returns all sales transactions beginning at the start of the quarter until the current date.