Tip of the Day : How to Join with an Inline User-Defined Function
Get First Day of the Week Function

Get First Day of the Week Function

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

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