sqlserver 2000 date - 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 2000 Forums

SQL Server Development (2000)

sqlserver 2000 date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:07:06

pradeep m.m. writes "hi,

i have 2 date values like '1/25/2002' and '5/25/2002'
and i need to find out how many fridays occur between these dates
as these dates are not stored in any table this is a user input

answer as quickly as possible

bye
pradeep"

macka
Posting Yak Master

162 Posts

Posted - 2002-06-27 : 11:45:55

Only Fridays between these dates ??

What if date1 and date2 both fall on Fridays ? Should they both be included - or just one - or none ?

macka.

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-06-27 : 12:07:32

quote:

Only Fridays between these dates ??

What if date1 and date2 both fall on Fridays ? Should they both be included - or just one - or none ?

macka.

Here's my stab at it.

Create proc Friday(@date1 datetime,@date2 datetime, @Fridays int output)
AS
DECLARE @Firstday int
Set @Firstday=datepart(dw,@date1)

--counts 1 friday for every FULL week
set @fridays=cast(@date2-@date1 as integer)/7

--adds an additional friday after comparing the start day to the
--number of days in any 'fractional' weeks remaining

set @fridays=@fridays+Case when (cast((@date2-@date1) as integer)%7)+@Firstday>5 then 1 else 0 end
--If you DON'T want to include the last Friday, change )+(@Firstday%7)>5 ---to @Firstday>6

Call it as-

Declare @fred int
exec Friday '7/5/2002','7/12/2002',@fred output
Select @Fred

Edited by - kevin snow on 06/27/2002 12:09:52

Edited by - kevin snow on 06/27/2002 14:58:10

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-06-27 : 15:03:57

OOPS!

Sorry. Screwed it up for first day being a Saturday.
modified "+ @firstday" to "+ (@Firstday%7)">

This includes the Friday of the start date, and the end date if it is also Friday.

mpradeep
Starting Member

1 Post

Posted - 2002-06-27 : 22:56:57

@stdate datetime,
@eddate datetime,
declare @fwkday varchar(10)
declare @day varchar(10)
declare @cnt int

select @cnt=0

select @fwkday='Friday'

WHILE (@stdate<=@eddate)
BEGIN
select @day=(select datename(dw,@stdate))

if (@day=@fwkday)
begin
select @cnt=@cnt +1
end
select @stdate=(SELECT DATEADD(day,1,@stdate))

IF (@stdate > @eddate)
BREAK
ELSE
CONTINUE

END

print @cnt
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources