June 14, 2011 at 6:10 am
DECLARE @s-2 datetime
DECLARE @e datetime
SELECT @s-2 = '2011-05-20', @e = '2011-06-30'
DECLARE @d table (
UniqId int identity(1,1),
Startday datetime,
Endday datetime,
WeekNumber int
)
if (datename(dw, @s-2)='MONDAY')
set @s-2=@s
else
if(datename(dw, @s-2)='TUESDAY')
set @s-2=DATEADD(dd,-1,@s)
else
if(datename(dw, @s-2)='WEDNESDAY')
set @s-2=DATEADD(dd,-2,@s)
else
if(datename(dw, @s-2)='THURSDAY')
set @s-2=DATEADD(dd,-3,@s)
else
if(datename(dw, @s-2)='FRIDAY')
set @s-2=DATEADD(dd,-4,@s)
else
if(datename(dw, @s-2)='SATURDAY')
set @s-2=DATEADD(dd,-5,@s)
else
if(datename(dw, @s-2)='SUNDAY')
set @s-2=DATEADD(dd,-6,@s)
while (@s<=@e) begin
IF (datename(dw, @s-2)='MONDAY')
Begin
insert into @d(Startday,Endday,WeekNumber)
values (@s,DATEADD(dd,4,@s),datepart(wk,@s))
SET @s-2=DATEADD(dd,7,@s)
END
END
SELECT * FROM @d
June 14, 2011 at 6:54 am
Why have you posted this script?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 15, 2011 at 12:09 am
I think you saw the output.
Can i know any other way to get same output.
June 15, 2011 at 7:52 am
You're very long if structure can be replaced with a simple formula.
SELECT DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)
The formula takes advantage of the fact that date 0 fell on a Monday.
The loop can be replaced with a tally (or numbers) table which Jeff describes in this article http://www.sqlservercentral.com/articles/62867/
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 15, 2011 at 11:32 pm
I asked how to get weekdays between two given dates.
Results will display in table like
startday (monday_date) , endday (friday_date) ,week_number
June 16, 2011 at 6:05 am
The easiest way with with a calendar table. See....
Simi
June 18, 2011 at 3:53 pm
Hi,
Try below script. It will loop through weeks between given dates and produce the output as you posted earlier.
SET DATEFIRST 1
DECLARE @weekDays TABLE (
UniqId int IDENTITY(1,1)
, StartDay datetime
, EndDay datetime
, WeekNo int
)
DECLARE @startDate date, @endDate date, @index int
SET @startDate = '2011-05-20'
SET @endDate = '2011-06-30'
SET @index = DATEPART(week, @startDate)
WHILE @index <= DATEPART(week, @endDate)
BEGIN
INSERT INTO @weekDays (StartDay, EndDay, WeekNo)
SELECT DATEADD(day, -(DATEPART(weekday, @startDate) - 1), @startDate) AS StartDay
, DATEADD(day, (7 - DATEPART(weekday, @startDate)) - 2, @startDate) AS EndDay --Exclude Sat and Sun
, DATEPART(week, @startDate) AS WeekNo
SET @startDate = DATEADD(week, 1, @startDate)
END
SELECT * FROM @weekDays
Few words about the script:
Tested on MS SQL 2008. I don't remember, if SQL 2005 had all used date part arguments like "weekday" etc which helps here and make the script shorter.
Very important is line SET DATEFIRST 1. It tells server that first day of a week is Monday. By default it might be set to 7 and then script would not return Mon-Fri range as you wanted.
Let me know, if that helps. It was interesting, at least for me, task to find solution for 🙂
June 20, 2011 at 8:22 am
Hi,
Its easier with Common table Expression. Hopefully, this will work for you.
USE tempdb
GO
SET LANGUAGE us_english
DECLARE @s-2 datetime
DECLARE @e datetime
SELECT @s-2 = '2011-05-20', @e = '2011-06-30'
DECLARE @d table (
UniqId int identity(1,1),
Startday datetime,
Endday datetime,
WeekNumber int
)
;With cte(n)
as
(
select @s-2 as mydate
union all
select n+1
from
cte
where
n<=@e
)
insert into @d(Startday,Endday,WeekNumber)
select case when datepart(dw, n) in (2) then n end, case when datepart(dw, n) in (2) then n+4 end,datepart(wk,case when datepart(dw, n) in (2) then n end)
from
cte
where
case when datepart(dw, n) in (2) then n end is not null
SELECT * FROM @d
GO
Cheers
June 20, 2011 at 9:22 am
--EDIT--
Have now tested. I've written it to emulate your description rather than your code. If it should be the other way around, let me know. To find out the best version of the three that have been posted to use, try each one between 2000-01-01 and 2100-12-31 and see what happens. Nothing like 100 years worth of weeks to separate the queries 😉
DECLARE @starttime DATETIME, @endtime DATETIME
SET @starttime = '2011-05-20'
SET @endtime = '2011-06-30'
--Tally table would be better, for testing purposes I've included one here "on the fly"
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
t6 AS (SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@starttime) AS startday
FROM t4 x, t4 y),
tally AS (SELECT startday
FROM t6
WHERE startday <= @endtime
AND startday = DateAdd(Week, DateDiff(Week, 0, startday), 0))
--Actual code
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS uniqid,
startday, DATEADD(DAY,4,startday) AS endday, DATEPART(WEEK,startday) AS weeknumber
FROM tally
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply