Dates minus weekend - 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

General SQL Server Forums

New to SQL Server Programming

Dates minus weekend

Author

Topic

msimmons
Starting Member

2 Posts

Posted - 2011-04-25 : 20:22:37

So I'm fairly new to this. I'm trying to grab two days, a start and end day, but if that lands on a weekend then not count it. Below is a tempt table I've created. So I'm taking todays date and then subtacting one or two days to get my range and check to see if it is a weekend. If it is it changes the day to the previous Friday. Problem I'm having is if both days land on the weekend then it is returning friday for both days. Any help would be great.

insert into #PeriodTable

(
Description,
StartDate,
EndDate,
SortOrder
)

Values
(
'2 Day Actual',
DATEADD(day,0,dbo.fnsetDay(@BaseDate - 2)),
DATEADD(day,0,dbo.fnsetDay(@BaseDate - 1)),
5
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 20:39:55

Isn't that the correct behavior based on what you described? If start and end dates being on the same day is not acceptable, then may be your rules should be to advance the end date to Monday and retract the start date to Friday if on a weekend?

msimmons
Starting Member

2 Posts

Posted - 2011-04-25 : 20:44:37

How it behaves when it is not a weekend is for example would return start day 4/21/2011 00:00:00 and end day at 4/22/2011 00:00:00, but when it is weekend as today this happened to me, it returns start day 4/22/2011 00:00:00 and end day at 4/22/2011 00:00:00 so I'm not capturing any data.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 21:02:59

Prior to SQL 2008, the only date/time data types available in SQL were datetime and smalldatetime. Each of these stores the date and the time in a single variable. So when you declare a datetime variable and assign a date to it, it is really storing the date along with time as the midnight of that day. For example:

declare @date datetime;
set @date = '20110424'; -- this is midnight of April 24

People use couple of different methods to work within this limitation. For example, you could say that your where condition would be like this:


where @date >= StartDate and @date < dateadd(day,1,EndDate)

This is saying "if @date is greater than or equal to the midnight of StartDate and @date is less than the midnight of the day following EndDate".

So if you do it that way, in your example, where start day is at 4/22/2011 00:00:00 and end day is at 4/22/2011 00:00:00, you will be picking up everything that falls between 4/22/2011 00:00:00 and 4/22/2011 23:59:59 (approximately so depending on whether you are using datetime or smalldatetime).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 21:11:47

BTW, my favorite method for moving from a weekend to the prior Friday or next Monday is as follows:

declare @date datetime; set @date = '20110424';

-- to move a date to the previous Friday
select 
   case 
      when datediff(dd,0,@date)%7 > 4 then dateadd( dd, 4-datediff(dd,0,@date)%7 ,@date) 
   else 
      @date 
   end;

-- to move a date to the following Monday
select 
   case 
      when datediff(dd,0,@date)%7 > 4 then dateadd( dd, 7-datediff(dd,0,@date)%7 ,@date) 
   else 
      @date 
   end;

The nice thing about it is that it will work correctly regardless of what locale settings you have, what date format you are using, and which day of the week your installation of SQL considers as the first day of the week etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources