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