sql date question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sql date question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-31 : 18:26:30

My requirement is if the startdate and enddate parameters are null, then the dates should default to last week,
sunday(startdate) - saturday(enddate),otherwise, it should take the supplied parameters.

I am planning to write the below code for that, please let me know if there is a better way of doing that. Thanks.

create proc proc1
@startdate datetime = null,
@enddate datetime = null
as
IF @STARTDATE IS NULL AND @ENDDATE IS NULL
IF DATENAME(dw, GETDATE()) = 'Sunday'
SET @STARTDATE = getdate()-7 --SUNDAY
SET @ENDDATE = GETDATE()-1 --SATURDAY
IF DATENAME(dw, GETDATE()) = 'Monday'
SET @STARTDATE = getdate()-8 --SUNDAY
SET @ENDDATE = GETDATE()-2 --SATURDAY

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-31 : 19:38:32

You could do it as follows:

if @startdate is null and @enddate is null
begin
   set @startdate = DATEADD(day, DATEDIFF(day, 0,GETDATE()) /7*7, -1);
   set @enddate = dateadd(day,@startdate,-1);
end

For the case where getdate() returns a Sunday, if you want @startdate to default to that Sunday (rather than the previous Sunday), you would need to add more code.

BTW, this calculation relies on the fact that date 0 (which implicitly converts to January 1, 1900) was a Monday, and so the result you get from taking datediff between that date and any Monday is divisible by 7.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources