Wednesday 16 May 2012

Business Week Functions (T-SQL)

Recently I was faced with a little problem concerning the dates defining a business week. That is, given any date, return the values of the Monday and Sunday of that week. After some experimentation with DateAdd(), I discovered the solution, but the syntax was a little awkward and difficult to remember, so I decided to encapsulate it in a pair of functions:

BOW() -- beginning of the week
EOW() -- end of the week


These are simple but useful functions. I assumed the "standard" definition of a business week, that is, Monday begins the week and Sunday ends it. Here's the code:



-- =============================================
-- Author: Arthur Fuller
-- Create date: 2012-05-16
-- Description: Returns beginning of the week
-- =============================================
CREATE FUNCTION [dbo].[BOW] 
(
@d DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @Result DateTime
SELECT @Result = DATEADD(wk,DATEDIFF(wk,0,@d),0) 
RETURN @Result
END
GO



-- =============================================
-- Author: Arthur Fuller
-- Create date: 2012-05-16
-- Description: Returns end of week
-- Notes: Assumes week runs from Monday to Sunday
-- =============================================
CREATE FUNCTION [dbo].[EOW] 
(
@d DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @Result DateTime
SELECT @Result = DATEADD(wk,DATEDIFF(wk,0,@d),6)
RETURN @Result
END
GO

A couple of notes:
You could declare a default for the parameter, such as GetDate().
You could declare a second parameter to flag whether the week begins on Sunday or Monday. I didn't bother because I'm assuming that whichever it is, the setting applies for the entire database
Feel free to use them anytime you have a similar need.



No comments:

Post a Comment