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