Get First Day of the Week Function and Get First Day of the Month Function

 

Get First Day of the Week Function

 

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

SELECT * FROM [dbo].[Orders]
WHERE [OrderDate] >= [dbo].[ufn_GetFirstDayOfWeek] ( GETDATE() )

Get First Day of the Month Function

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

    RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

END
GO

SELECT * FROM [dbo].[Orders]
WHERE [OrderDate] >= [dbo].[ufn_GetFirstDayOfMonth] ( GETDATE() )