date function - 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)

date function

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-05 : 09:19:01

Dear gurus,

How do get Saturdays & sundays in between the given dates.

Thanks in advance

cool...,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-05 : 09:28:22

One of the methods is

Select columns from table
where datecol between @begindate and @enddate
and datename(weekday,datecol) in ('saturday','sunday')

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-05 : 09:35:15

I think Madhis way is more easier than mine.You can then try this one also.

declare @tbl table(dt1 datetime,dt2 datetime)
insert into @tbl
select '01-jun-2008','10-jun-2008' union all
select '01-jul-2008','05-jul-2008'

select dt from(
select dateadd(dd,number,dt1)as dt,number,dt1 from
master.dbo.spt_values inner join @tbl on type='p'
where dateadd(dd,number,dt1)<=dt2
)t where
datename(weekday, dateadd(dd,number,dt1))='Saturday' or datename(weekday, dateadd(dd,number,dt1))='Sunday'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-05 : 11:19:41

Select columns from table
where datecol between @begindate and @enddate
and DATEPART(weekday,datecol)^2 & 224 > 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 11:30:28

DATEPART is dependant on SET DATEFIRST setting, and might yield different result on other databases.

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