Yet another attempt to build up a library of SQL Server functions. As I found out, not all my previous functions did work properly in all environments.
-- finds start of current week for the provided date
CREATE FUNCTION [dbo].[fnStartOfWeek]
(
@INPUTDATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
-- THIS does not work in function.
-- SET DATEFIRST 1 -- set monday to be the first day of week.
DECLARE @DOW INT -- to store day of week
SET @DOW = DATEPART(DW, @INPUTDATE)
-- Magic convertion of monday to 1, tuesday to 2, etc.
-- irrespect what SQL server thinks about start of the week.
-- But here we have sunday marked as 0, but we fix this later.
SET @DOW = (@DOW + @@DATEFIRST - 1) %7
IF @DOW = 0 SET @DOW = 7 -- fix for sunday
RETURN dbo.fnStartOfDay( DATEADD(DD, 1 - @DOW,@INPUTDATE) )
END
GO
-- finds end of the current week for the provided date
CREATE FUNCTION [fnEndOfWeek]
(
@INPUTDATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @WEEKSTART DATeTIME,
@WEEKEND DATETIME
SET @WEEKSTART = dbo.fnStartOfWeek(@INPUTDATE) -- Find start of this week.
SET @WEEKEND = DATEADD(week,1, @WEEKSTART) -- add one week to the start of the week.
SET @WEEKEND = DATEADD(second, -1, @WEEKEND) -- take out one second from the end of the week
RETURN @WEEKEND
END
GO
-- Finds end of the day for the provided date
CREATE FUNCTION [fnEndOfDay]
(
@DATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(s,-1,DATEADD(d, DATEDIFF(d,0,@DATE)+1,0))
END
GO
-- Finds Start of the day for the provided day
CREATE FUNCTION [fnStartOfDay]
(
@DATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
Return DATEADD(d, DATEDIFF(d,0,@DATE),0)
END