Every monday in date range

Question

Post reply

Every monday in date range

John Horne

SSC Veteran

Points: 232
More actions
March 9, 2007 at 8:03 am

#171616

Hello everyone,
I need a single sql statement that will return every monday within a date range. For example lets say my date range is from 1-1-2007 to 2-13-2007. The sql statement will need to retrun 6 rows of 1-1-2007, 1-8-2007, 1-15-2007, 1-22-2007, 1-29-2007, 2-5-2007, and 2-12-2007, My sql experience is good with just pulling data from tables but to get this calculation from sql is causing me a lot of pain. Can anyone please help.
Thanks for the help in advance
john.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

There's probably a better way to do this, but here's my solution...

SET NOCOUNT ON

DECLARE @DateTable TABLE ( ADate DATETIME )

DECLARE @CurrentDate DATETIME

SET @CurrentDate = '01/01/2007'

WHILE @CurrentDate <= '02/13/2007'

BEGIN

INSERT INTO @DateTable

SELECT @CurrentDate

SET @CurrentDate = DATEADD( dd, 1, @CurrentDate )

END

SELECT * FROM @DateTable WHERE DATEPART( dw, ADate ) = 2

DELETE @DateTable

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

Well, using CTE's (note today's article) I came up with this solution:

DECLARE @start_dateDATETIME

,@end_dateDATETIME

SELECT @start_date = '01/01/2007'

,@end_date = '02/13/2007';

WITHCTE_Dates(Monday) AS (

SELECTCASE

WHEN DATEPART(dw, @start_date) <= 2 THEN DATEADD(dd, 2 - DATEPART(dw, @start_date), @start_date)

ELSE DATEADD(dd, 9 - DATEPART(dw, @start_date), @start_date)

END AS Monday

UNION ALL

SELECTDATEADD(dd, 7, Monday)

FROMCTE_Dates

WHEREDATEADD(dd, 7, Monday) <= @end_date

)

SELECT Monday FROM CTE_Dates

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 3

I made my query more efficient by only inserting the Mondays into the temporary date table in the first place...

SET NOCOUNT ON

DECLARE @DateTable TABLE ( ADate DATETIME )

DECLARE @StartDate DATETIME

DECLARE @CurrentDate DATETIME

SET @StartDate = '01/01/2007'

--Replace the 2 below with whichever day of the week you are looking for (1=Sunday, 2=Monday, .... 7=Saturday)

SET @CurrentDate = DATEADD( dd, 2 - DATEPART( dw, @StartDate ), @StartDate )

IF @CurrentDate < @StartDate

BEGIN

SET @CurrentDate = DATEADD( dd, 7, @CurrentDate )

END

WHILE @CurrentDate <= '02/13/2007'

BEGIN

INSERT INTO @DateTable

SELECT @CurrentDate

SET @CurrentDate = DATEADD( dd, 7, @CurrentDate )

END

SELECT * FROM @DateTable

DELETE @DateTable

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

This returns all Mondays from 2000-01-01 through 2010-12-31

select
 a.Date
from
 -- Function F_TABLE_DATE available on this link
 -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
 dbo.F_TABLE_DATE ( '20000101','20101231' ) a
where
 a.DAY_OF_WEEK = 2
order by
 a.Date

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Here's another take on the problem, and you don't need to be dependent on @@DATEFIRST settings either.

--==== return weekday of @myDate regardless of @@DATEFIRST settings

--==== 1 = Monday

--==== 2 = Tuesday

--==== 3 = Wednesday

--==== 4 = Thursday

--==== 5 = Friday

--==== 6 = Saturday

--==== 7 = Sunday

declare @myDate char(8)

set @myDate = '20060102'

select ( @@datefirst + datepart(weekday, @mydate) -2 ) %7 + 1

So, you could use something like this to return those mondays - either from a table with actual dates, or from a datetable that contains a list of all dates.

SELECT *

FROM myTable

WHERE myDatecol BETWEEN '20070101' AND '20070213'

AND (( @@datefirst + datepart(weekday, myDatecol) -2 ) %7 + 1 ) = 1

Just change the discrimantor (in bold) to whichever digit you need according to which weekday(s) you want listed

=;o)

/Kenneth

Camilo Torres SSCrazy Points: 2034 More actions · Answer 7

Check this SP out

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description:

-- Returns Day of Week in date range

-- from: @tablename

-- based on column: @DateField

-- =============================================

CREATE

PROCEDURE _GetDayOfWeek_inRange(

@tablename

nvarchar(max)

,@DateField nvarchar(max)

,@DayName nvarchar(max)

,@dtFrom nvarchar(max)

,@dtTo nvarchar(max)

,@ErrMsg nvarchar(max) OUTPUT)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare

@SQLCommand

nvarchar(max)

,@Ok bit

set @ErrMsg = case when (@tablename is null) or (@DateField is null) then

'You need to pass a table_name and field to get dates from'

when (@dtFrom is null) or (@dtTo is null) then

'Specify a valid Date Range'

else ''

end

set @Ok = case when (@ErrMsg = '') then 1 else 0 end

if @Ok = 1

begin

set @SQLCommand = 'select ' + @DateField + ' as ' + @DayName +'s from ' + @tablename

+ ' where ' + @DateField + ' between '

+ char(39) + @dtFrom + char(39) + ' and ' + char(39) + @dtTo + char(39)

+ 'and datename(dw,' + @DateField + ') = ' + char(39) + @DayName + char(39)

exec sp_executesql @SQLCommand

end

END

GO

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

Hmmm... anyone know of any disadvantages of specifying VARCHAR(MAX) for everywhere a varchar is needed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)