weekend dates - 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

weekend dates

Author

Topic

pmccann1
Posting Yak Master

107 Posts

Posted - 2008-06-24 : 06:55:16

I was wondering could someone help me. i have two dates a start_date and an end_date i need to work out if a weekend falls with in the dates. can this be done or is there an easy way of working it out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 07:06:26

A full weekend or only a part of a weekend.
And your weekend is defined as saturday and sunday?

E 12°55'05.25"
N 56°04'39.16"

pmccann1
Posting Yak Master

107 Posts

Posted - 2008-06-24 : 07:09:18

i need to know if a saturday and sunday are within the dates

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-24 : 07:17:31

Something like this maybe:

Declare @sd datetime, @ed datetime, @res varchar(10)

select @sd = dateadd(day, datediff(day, 0, getdate()), 0) , @ed = dateadd(day,3 , dateadd(day, datediff(day, 0, getdate()), 0))

print 'start date: ' + cast(@sd as varchar(20)) + '  End Date: '  +  cast(@ed as varchar(20)) 
if exists(select * from master..spt_values where name is null and dateadd(day, [number], @sd) <= @ed and datename(weekday, dateadd(day, [number], @sd))  in ('saturday', 'sunday') )
print 'yes'
else
print 'no'

Note: You will have to use custom number table if date difference between two dates is more than 255 days

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 07:17:42

And with that you mean any part of s saturday or any part of a sunday, or do you mean a complete saturday and sunday, or a complete saturday or a complete sunday?

Which version of SQL Server are you using?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 07:17:55

[code]SET DATEFIRST 1

SELECT CASE WHEN COUNT(*)>0 THEN 'Weekend Overlap' ELSE 'No Overlap' END
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@Start)<=@End
AND DATEPART(dw,DATEADD(dd,number,@Start)) IN (6,7)[/code]

pmccann1
Posting Yak Master

107 Posts

Posted - 2008-06-24 : 07:27:43

any part of a saturday or sunday

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 08:05:47

[code]CREATE FUNCTION dbo.fnWeekendBetween
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS BIT
AS
BEGIN
IF DATEDIFF(DAY, @StartDate, @EndDate) NOT BETWEEN -4 AND 4
RETURN 1

IF @StartDate > @EndDate
SET @StartDate = @EndDate

IF DATENAME(WEEKDAY, @StartDate) IN ('Saturday', 'Sunday')
RETURN 1
ELSE IF DATENAME(WEEKDAY, DATEADD(DAY, 1, @StartDate)) IN ('Saturday', 'Sunday')
RETURN 1
ELSE IF DATENAME(WEEKDAY, DATEADD(DAY, 2, @StartDate)) IN ('Saturday', 'Sunday')
RETURN 1
ELSE IF DATENAME(WEEKDAY, DATEADD(DAY, 3, @StartDate)) IN ('Saturday', 'Sunday')
RETURN 1
ELSE IF DATENAME(WEEKDAY, DATEADD(DAY, 4, @StartDate)) IN ('Saturday', 'Sunday')
RETURN 1

RETURN 0
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources