|
|
It is quite common for applications to produce a report that contains
transactions or records beginning from the start of the week until the current
day. Getting the current day is easy because this is achieved from the
GETDATE() function but getting the start of the week is a little bit
tricky. The user-defined function below accepts a date input and returns
the first day of the week for that input date.
CREATE FUNCTION [dbo].[ufn_GetFirstDayOfWeek]
( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),
@pInputDate)
END
GO
|
Description
Getting the first day of the week is basically straight-forward once you know
the trick. Each day of the week is represented by a number from 1 to 7,
with 1 being the first day of the week. Given any day of the week, to get
back to the first day, you simply have to subtract from the current day the
number of days equal to the day of the week then add 1 day.
For example, if today is the fifth day of the week, to get back to the first
day, subtract 5 days from the current day and then add 1. So, if today is
'09/01/2005', a Thursday, which is the fifth day of the week in the U.S.
English calendar, subtracting 5 days from this date becomes '08/27/2005' then
adding 1 day becomes '08/28/2005', a Sunday, which is the first day of the week
for the date '09/01/2005'.
-
SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
- The first step is to get the date part of the input parameter because since
it is defined as a DATETIME data type, it may contain a time part.
Converting the input date into VARCHAR(10) with a format of 101, which is in
"YYYY/MM/DD" performs the task of getting the date part of the input
date. Since the receiving variable is defined as a DATETIME data type,
there is no need to explicitly CAST this back to DATETIME because SQL Server
will implicitly convert it to DATETIME data type.
-
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate) - This
step is responsible in deriving the first day of the week for the given input
date. It basically subtracts from the input date the number of days equal
to the day of the week of the input date then adds 1 day to it. It may be
confusing to see how the day of the week is subtracted from the input date then
another day is added with the formula above. Another way of writing the
above statement is DATEADD(DD, -DATEPART(DW, @pInputDate) + 1, @pInputDate),
which is the equivalent of subtracting the day of the week plus 1 day.
Getting the date part only of any DATETIME or SMALLDATETIME data type is quite a
common task that it is recommended to create a separate user-defined function
just for this task. The user-defined function below is the same as the
one above with the exception that it assumes that a user-defined function that
extracts the date part only of a DATETIME or SMALLDATETIME data type exists
with a name of [dbo].[ufn_GetDateOnly].
CREATE FUNCTION [dbo].[ufn_GetFirstDayOfWeek] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
SET @pInputDate = [dbo].[ufn_GetDateOnly] ( @pInputDate )
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)
END
GO
Usage
Here's an example of how to use this user-defined funcation:
SELECT * FROM [dbo].[Orders]
WHERE [OrderDate] >= [dbo].[ufn_GetFirstDayOfWeek] ( GETDATE() )
This statement returns all orders from the start of the week..
|