About Me

Wednesday, December 1, 2010

Get closest Monday SQL function

From time to time I have to build reports for the application I'm working on.
Today they asked me to build the report that can accept any date and build results within Monday to Sunday.

The problem was, how to find date of Monday, since the user may enter any week date.
So, for instance, if the user enters '12/03/10' , for instance, that Friday, the system should find date of Monday '11/29/10'. And so on...

I wrote small sql function that helps to get it:

if exists (select * from sysobjects where name = 'GetMonday')
    drop function GetMonday
go

create  function [dbo].[GetMonday](@date DateTime)
-- Returns closest Monday date that going before passed date
returns datetime
as
begin
    declare @datePart int
    set @datepart = datepart(weekday, @date )

    --in case if sunday treats the system as first day of the week,
    --convert passed sunday date into the last day of the week
    -- 12/26/10 was sunday, so we are testing first what day of the week it returns
    if (datepart(weekday, '12/26/10') = 1 AND @datepart = 1)
    SET @datepart = 8

    return dateadd(dd, (@datePart - 2) * -1, @date)
end
go

select dbo.GetMonday(getdate())

No comments:

Post a Comment