Dates wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of wee
ks?
The tricky thing is that the script needs to account for ws where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17NH
Can you provide DDL+ sample data + expected result?
CREATE TABLE bbb
(
dt DATETIME,
...
....
)
"NH" <NH@.discussions.microsoft.com> wrote in message
news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
> Anyone got a script to get the start-dates and end-dates for x number of
> ws?
> The tricky thing is that the script needs to account for ws where the
> monday date is in say April and the Friday date is in May. In this case
> the
> start-date is the monday but the end-date may be a wednesday or whenever
> the
> last date of the month is. In other cases the start-date wont be the
> monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17|||What criterion/criteria determine whether your start-date is a Monday
or Wednesday?
If the end-date is a Wednesday, is the start-date then a Thursday?
Etc.
Andrew Watt [MVP]
On Mon, 24 Apr 2006 04:57:02 -0700, NH <NH@.discussions.microsoft.com>
wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
>I need a result set like this...
>Start-date End-date
>2006-01-30 2006-01-31
>2006-02-01 2006-02-03
>2006-02-06 2006-02-10
>2006-02-13 2006-02-17
>2006-02-20 2006-02-24
>2006-02-27 2006-02-28
>2006-03-01 2006-03-03
>2006-03-06 2006-03-10
>2006-03-13 2006-03-17|||this is the table that the results will go into...
CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
[key] [int] IDENTITY (1, 1) NOT NULL ,
[startdate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
I want to fill it with dates between 2006 and 2050.
Basically the start date will always be the monday, unless the any months
start date is some other day of the w. Same for end dates, they are
usually friday dates but a month end date could be a tuesday etc and this
needs to be recorded.
If you look at the sample data in my first post you should be able to see
the way it should work...? Does this make sense?
"Uri Dimant" wrote:

> NH
> Can you provide DDL+ sample data + expected result?
> CREATE TABLE bbb
> (
> dt DATETIME,
> ...
> .....
> )
>
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
>
>|||try this and let me know if this was what you wanted.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-30'
set @.enddate = '2006-03-17'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 1 and 5
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
drop table #temp|||A technical bug in my solution. date difference between 1 and 5 changed to 1
and 4.Use this.. updated.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-01'
set @.enddate = '2050-01-01'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 0 and 4
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
order by dateadd(dd, a.id,@.startdate)
drop table #temp|||NH
Read please this article helps you to get an idea
http://www.aspfaq.com/show.asp?id=2519
"NH" <NH@.discussions.microsoft.com> wrote in message
news:0CAF9FBF-E162-48FF-9661-971551CE2D1A@.microsoft.com...
> this is the table that the results will go into...
> CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
> [key] [int] IDENTITY (1, 1) NOT NULL ,
> [startdate] [datetime] NULL ,
> [enddate] [datetime] NULL
> ) ON [PRIMARY]
> I want to fill it with dates between 2006 and 2050.
> Basically the start date will always be the monday, unless the any months
> start date is some other day of the w. Same for end dates, they are
> usually friday dates but a month end date could be a tuesday etc and this
> needs to be recorded.
> If you look at the sample data in my first post you should be able to see
> the way it should work...? Does this make sense?
> "Uri Dimant" wrote:
>|||Hi all
Omnibuzz - there are some duplicates in that for me (e.g. 2006-04-03), but
I'm not sure what the problem is :(
Here's my stab... :)
--inputs
declare @.startdate datetime, @.enddate datetime
set @.startdate = '20060130'
set @.enddate = '20500101'
set datefirst 7
--calculation
declare @.NumberOfDays int
set @.NumberOfDays = datediff(d, @.startdate, @.enddate) + 1
set rowcount @.NumberOfDays
declare @.numbers table (i int identity(0,1), x bit)
insert into @.numbers select null from master.dbo.sysobjects a,
master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0
select d as StartDate,
case when datepart(day, d) = 1 --start of month
then dateadd(day, 6-datepart(dw, d), d)
when datepart(month, d) != datepart(month, d+4) --end of month
then dateadd(month, datediff(month, 0, d+4), 0)-1
else --normal w
d+4
end as EndDate
from
(select dateadd(dd, i, @.startdate) d from @.numbers) dates
where d = @.startdate --don't miss start date
or datepart(dw, d) = 2 --monday
or (datepart(day, d) = 1 and datepart(dw, d) between 3 and 6) --1st of
month and tue-fri
"Omnibuzz" wrote:

> A technical bug in my solution. date difference between 1 and 5 changed to
1
> and 4.Use this.. updated.
> select identity(int,0,1) as id into #temp from sysobjects
> declare @.startdate datetime, @.enddate datetime
> set @.startdate = '2006-01-01'
> set @.enddate = '2050-01-01'
> select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) a
s
> eow from #temp a, #temp b
> where
> datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
> between 0 and 4
> and (
> (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
> dateadd(dd, b.id,@.startdate)) = 6 )
> or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
> datepart(dd,dateadd(dd, b.id,@.startdate)) =
> datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
> b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-
' +
> cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
> datetime))))
> or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
> dateadd(dd, a.id,@.startdate)) = 1 ))
> and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd
,
> b.id,@.startdate))
> and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
> a.id,@.startdate) <= @.enddate
> order by dateadd(dd, a.id,@.startdate)
> drop table #temp|||Hi Ryan,
Thanks for pointing it out, though I haven't checked it yet. I am back
home now. Will check it and post the update, if necessary, tomorrow.|||On Mon, 24 Apr 2006 04:57:02 -0700, NH wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
(snip)
Hi NH,
Assuming that you already have a table of numbers in your database,
here's how you could fill your table quickly:
DECLARE @.StartOfPeriod smalldatetime
DECLARE @.EndOfPeriod smalldatetime
SET @.StartOfPeriod = '20060101'
SET @.EndOfPeriod = '20081231'
CREATE TABLE Ws
(StartDate smalldatetime NOT NULL PRIMARY KEY,
EndDate smalldatetime NOT NULL
)
-- Step 1: Generate full ws (mon-fri)
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(w, Number, '20050103'),
DATEADD(w, Number, '20050107')
FROM Numbers
WHERE DATEADD(w, Number, '20050103') >= @.StartOfPeriod
AND DATEADD(w, Number, '20050107') <= @.EndOfPeriod
-- Step 2a: For ws that span a month, add second partial w
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(day, 1 - DAY(EndDate), EndDate), EndDate
FROM Ws
WHERE MONTH(StartDate) <> MONTH(EndDate)
-- Step 2b: For ws that span a month, shorten first partial w
UPDATE Ws
SET EndDate = DATEADD(day, - DAY(EndDate), EndDate)
WHERE MONTH(StartDate) <> MONTH(EndDate)
SELECT * FROM Ws
go
Hugo Kornelis, SQL Server MVP