Expression to calculate last sunday

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Expression to calculate last sunday

Author

Topic

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-04 : 10:43:35

Hi, I need to write a expression in SSRS in the FROM Date to be the last sunday.

EG: If todays date is 9/4/2009 the in the FROM Date i want it to be 8/30/2009

If todays date is 8/19/2009 then the FROMDATE would be 8/16/2009

I dont know how to write this expression. Can someone help

sinclair
Starting Member

7 Posts

Posted - 2009-09-07 : 04:43:52

Below is a function I got a while ago from somewhere.

create function [dbo].[F_START_OF_WEEK]
(
@DATE datetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAY int = 1
)
/*
Find the first date on or before @DATE that matches
day of week of @WEEK_START_DAY.
*/
returns datetime
as
begin
declare @START_OF_WEEK_DATE datetime
declare @FIRST_BOW datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
begin
-- Find first day on or after 1753/1/1 (-53690)
-- matching day of week of @WEEK_START_DAY
-- 1753/1/1 is earliest possible SQL Server date.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
-- Verify beginning of week not before 1753/1/1
if @DATE >= @FIRST_BOW
begin
select @START_OF_WEEK_DATE =
dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
end
end

return @START_OF_WEEK_DATE

end

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources