# Thread: Working with dates and weeks...?

1. Registered User
Join Date
Nov 2010
Posts
20

## Unanswered: Working with dates and weeks...?

I'm trying to find a way to create a table for a list of Periods.
One period should be 2 weeks, from monday to sunday.
I'm working with Visual Basic also, but haven't managed to do a query in SQL that inserts the right kind of data... Need help on this one...

The table containing this list of Periods should have the "ID", "PeriodNumber", "StartDate" and "EndDate".
-ID is just an identity specification with an identity increment of 1 for every new row. (DONE)
-PeriodNumber is an integer that refers to the 2-week period. This number is supposed to reset every year. So there are always 26 or 27 periods per year.
-StartDate is the first date of the period which is a monday. (Going to work with somehing like:
WHERE DATENAME (DW, StartDate) = 'Monday' )
-EndDate is the last date of each period which is a sunday 13 days after the StartDate. (Going to work with something like:
UPDATE Periods
WHERE ID = @@IDENTITY

So,
-How can I determin the following periods to start every other monday?
-How can I determin the PeriodNumber? For example, now is week 51 of this year, which would be PeriodNumber 25. Next week would be PeriodNumber 26. Then number should not be determined by the last row, but by the week of the year...
-Is it possible to add the EndDate in the same query as the the StartDate if I decide to use the "(DATEADD(DAY,+13,StartDate)"?
-ANY other tips and advices are always welcome!

2. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
This may get you started:
Code:
```with cte as
(
select nbr = 1
union all
select nbr + 1
from cte
where nbr < datediff(day, cast(cast(year(getdate()) as char(4)) + '-01-01' as date), cast(cast(year(getdate())+1 as char(4)) + '-01-01' as date))
)
select
PayPeriod = row_number() over (order by nbr)
,DayOfWeek = datename(weekday, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
,CalDate = dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date))
,DayOfWeekIn2Weeks = datename(weekday, dateadd(day, nbr-1+13, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
,CalDateIn2Weeks = dateadd(day, nbr-1+13, cast(cast(year(getdate()) as char(4)) + '-01-01' as date))
,WeekNumber = datepart(week, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
,DayOfYear = datepart(dayofyear, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
from cte
where 1 = /* DayOfWeekNbr */ datepart(weekday, dateadd(day, nbr-1-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
and   1 = /* FirstSecond */ case datepart(week, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date))) % 2 when 0 then 1 else 2 end
option (maxrecursion 0)```

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
I have this function in sql

USE [Pars-SQL]
GO
/****** Object: UserDefinedFunction [dbo].[weekend] Script Date: 12/23/2010 10:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[weekend] (@we datetime)
RETURNS datetime
AS
BEGIN
RETURN DateAdd(dd, (-1 * Coalesce(NullIf(DateDiff(dd, 5, @we) % 7, 0), 7)) + 7, @we)
END

it will return a saturday date base on the date it is pass to to
Then you can group your data by week.