Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to get a list of friday dates between a date range

Posted on 2004-04-12
6
Medium Priority
?
347 Views
Last Modified: 2006-11-17
I'd like to return all friday dates between a given range.

For instance, the range might be 1/1/2004 to 4/12/2004.

How can I return all friday's between those dates?
0
Comment
Question by:babybird
6 Comments
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10809144
From an existing dataset or just a list of them?

if you want to return records from an existing table where a date field is between a range and on a friday, then this would work

where yourdate between '1/1/2004 ' and '4/12/2004'
and datepart(dw,yourdate) = 6  ----provided you kept the default week start of Sunday

if you want to generate a list, then this will work (this could be made easier, but this is fast and accurate )


declare @numweeks int
declare @startdate datetime
declare @enddate datetime
declare @printstring varchar(12)
declare @currdate datetime

set @startdate = '1/1/2004'
set @enddate = '4/12/2004'


select @numweeks = datediff(wk,@startdate,@enddate)

set @currdate = @startdate

while @currdate < @enddate begin
      if datepart(dw,@currdate) = 6 begin
            set @printstring = convert(char(12),@currdate,101)
            print @printstring
      end
      set @currdate = dateadd(d,1,@currdate)
end

0
 
LVL 3

Expert Comment

by:edwardsearch
ID: 10810799
You can use table variable to return as result set

declare @ltbl table (tmpdate datetime)
declare @startdate datetime
declare @enddate datetime
declare @printstring varchar(12)
declare @currdate datetime

set @startdate = '1/1/2004'
set @enddate = '4/12/2004'

set @currdate = @startdate

while @currdate < @enddate
begin
 if datepart(dw,@currdate) = 6
    insert into @ltbl (tmpdate) values (@currdate)
  set @currdate = dateadd(d,1,@currdate)
end
select tmpdate from @ltbl

- Edward
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10811190
Hi babybird,

declare @test datetime

set @test = @startdate

select @test as Fri into @friday where 0=1

While @test <= @Enddate
begin
    if datename(dw,@test) = 'FRIDAY'
    begin
        insert into @Friday Values(@test)
    end
    Set @test=Dateadd(d,1,@test)
end

Select * from @friday
 order by 1

Cheers!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
Hilaire earned 1000 total points
ID: 10811228
Create Function dbo.GetFridays(@StartDate DateTime, @EndDate datetime)
declare @fridays table (f_date datetime)
AS
Begin
      set @StartDate = DateAdd(d, (20-@@datefirst-datepart(dw, @StartDate))%7, @StartDate)
      while @StartDate <= @EndDate
      begin
            insert into @fridays(f_date) values(@StartDate)
            set @StartDate = dateadd(d,7,@StartDate)
      end
      return
End
Go

select * from dbo.getfridays('2004.01.01', '2004.04.12' )

0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10811312
The code searches for the next friday and then steps week per week instead of day per day

DateAdd(d, (20-@@datefirst-datepart(dw, @StartDate))%7, @StartDate) finds next friday
It is not affected by date settings, ie it will still work even if you issue a
SET DATEFIRST    statement with non-default values

HTH

Hilaire
0
 

Author Comment

by:babybird
ID: 10824131
Thanks to all who helped!!  I learned something from all your methods.  That's why I love this forum so much....I always learn new and valuable things.  

I decided to go with the function call...just fyi for anyone that might utilize this code....to get it to compile and run, I changed the declare statement to a returns statement and it worked fine.

Thanks again!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question