Number of weeks between two dates

Question

Post reply

Number of weeks between two dates

Rem70Rem

SSCertifiable

Points: 6166
More actions
January 5, 2011 at 3:52 pm

#243225

.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Does this help?

Basically, the idea is to have another column that counts the weeks between two dates.

--= test data

DECLARE @t table(theDate smalldatetime, FinYear smallint, FinPer tinyint, FinWeek tinyint) ;

set nocount on ;

insert @t values('2009-12-27', 2010, 1, 1) ;

insert @t values('2009-12-28', 2010, 1, 1) ;

insert @t values('2009-12-29', 2010, 1, 1) ;

insert @t values('2010-01-19', 2010, 1, 4) ;

insert @t values('2010-01-20', 2010, 1, 4) ;

insert @t values('2010-02-18', 2010, 2, 8) ;

insert @t values('2010-02-19', 2010, 2, 8) ;

insert @t values('2010-03-25', 2010, 3, 13) ;

insert @t values('2010-03-26', 2010, 3, 13) ;

insert @t values('2010-04-30', 2010, 4, 18) ;

insert @t values('2010-05-01', 2010, 4, 18) ;

insert @t values('2010-05-28', 2010, 5, 22) ;

insert @t values('2010-05-29', 2010, 5, 22) ;

insert @t values('2010-06-28', 2010, 7, 27) ;

insert @t values('2010-06-29', 2010, 7, 27) ;

insert @t values('2010-08-27', 2010, 8, 35) ;

insert @t values('2010-08-28', 2010, 8, 35) ;

insert @t values('2010-09-15', 2010, 9, 38) ;

insert @t values('2010-09-16', 2010, 9, 38) ;

insert @t values('2010-09-29', 2010, 10, 40) ;

insert @t values('2010-09-30', 2010, 10, 40) ;

insert @t values('2010-12-16', 2010, 12, 51) ;

insert @t values('2010-12-17', 2010, 12, 51) ;

insert @t values('2010-12-18', 2010, 12, 51) ;

insert @t values('2010-12-19', 2010, 12, 52) ;

insert @t values('2010-12-20', 2010, 12, 52) ;

insert @t values('2010-12-26', 2011, 1, 1) ;

insert @t values('2010-12-27', 2011, 1, 1) ;

insert @t values('2010-12-28', 2011, 1, 1) ;

insert @t values('2011-03-03', 2011, 3, 10) ;

insert @t values('2011-04-03', 2011, 4, 15) ;

insert @t values('2011-05-05', 2011, 5, 19) ;

insert @t values('2011-05-28', 2011, 5, 22) ;

insert @t values('2011-06-18', 2011, 6, 25) ;

insert @t values('2011-06-19', 2011, 6, 26) ;

insert @t values('2011-07-10', 2011, 7, 29) ;

insert @t values('2011-08-08', 2011, 8, 33) ;

insert @t values('2011-08-29', 2011, 9, 36) ;

insert @t values('2011-10-08', 2011, 10, 41) ;

insert @t values('2011-11-21', 2011, 11, 48) ;

insert @t values('2011-11-22', 2011, 11, 48) ;

insert @t values('2011-12-27', 2011, 12, 53) ;

insert @t values('2011-12-28', 2011, 12, 53) ;

insert @t values('2011-12-29', 2011, 12, 53) ;

insert @t values('2011-12-30', 2011, 12, 53) ;

insert @t values('2011-12-31', 2011, 12, 53) ;

--= sample for dates 2010-12-18 and 2011-12-31 -

--= unfortunately the answer is wrong because all dates are not present between, but the idea works

select max(WeekNum) from (select DENSE_RANK() OVER(ORDER BY FinYear,FinPer,FinWeek) as WeekNum

from @t

where theDate BETWEEN '2010-12-18' AND '2011-12-31') as a

You could add a column like this to the table itself and just pull the difference between the two values by date to save some io cost.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 2

This is very interesting. But I will need to run this in two steps, since the date prior the current date need to be negative and after positive.

How I can apply this to the whole table. This give me only the information between tow date, but I need to add a new column to my table that will host these values.

Thanks a lot

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

Add a new column to your table e.g. RollingWeek smallint

update yourTable set RollingWeek=FinWeek where FinYear=2010

update yourTable set RollingWeek=FinWeek + 52 where FinYear=2011

update yourTable set RollingWeek=FinWeek + 105 where FinYear=2012

etc etc etc

then query for the difference between two dates :

e.g.

--= find weeks between 19th november 2010 and today 5th Jan 2011

select b.RollingWeek - a.RollingWeek as WeekDiff

from yourTable a, yourTable b

where a.theDate = '2010-11-19' AND b.theDate = '2011-01-05'

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 4

This seems to be a good approach, but some year have 52 weeks when some other have 53.

What I need to do it's adding a new volume to my table with the difference of number of weeks between the current date and the past and future date.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

Rem70Rem (1/6/2011)
This seems to be a good approach, but some year have 52 weeks when some other have 53.

Yes, you would have to deal with that manually when populating the RollingWeek column as in my example - there is no magic bullet for that unless you have a formula that can calculate whether there would be 52 or 53 weeks....

Rem70Rem (1/6/2011)
What I need to do it's adding a new volume to my table with the difference of number of weeks between the current date and the past and future date.

I don't know what you mean, sorry!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

JenMidnightDBA Right there with Babe Points: 787 More actions · Answer 6

mister.magoo (1/6/2011)

Rem70Rem (1/6/2011)
This seems to be a good approach, but some year have 52 weeks when some other have 53.

The DATEPART function has a WEEK datepart that displays the week of the year. You should be able to use this to dislpay the weeks of the year, if you use it with some custom code to account for

(1) your financial year's week 1,

(2) your financial week's starting day (e.g., does the week always begin on a Sunday?), and

(3) which year has 53 weeks instead of 52. While DATEPART(wk,...) takes into account years with 53 weeks, you need to make sure that this aligns with your financial calendar.

Disregarding all of that for the moment, a simple example of finding the week number for any given date would be:

SELECT DATEPART(WK,GETDATE())

And, to find the difference in weeks between two dates:

SELECT DATEDIFF ( WK , '1-1-2010', GETDATE())

BOL for DatePart: http://msdn.microsoft.com/en-us/library/ms174420.aspx

BOL for DateDiff http://msdn.microsoft.com/en-us/library/ms189794.aspx

-Jen

LutzM SSC Guru Points: 107049 More actions · Answer 7

JenMidnightDBA (1/6/2011)

mister.magoo (1/6/2011)
Rem70Rem (1/6/2011)
This seems to be a good approach, but some year have 52 weeks when some other have 53.
The DATEPART function has a WEEK datepart that displays the week of the year. You should be able to use this to dislpay the weeks of the year, if you use it with some custom code to account for
(1) your financial year's week 1,
(2) your financial week's starting day (e.g., does the week always begin on a Sunday?), and
(3) which year has 53 weeks instead of 52. While DATEPART(wk,...) takes into account years with 53 weeks, you need to make sure that this aligns with your financial calendar.
Disregarding all of that for the moment, a simple example of finding the week number for any given date would be:
SELECT DATEPART(WK,GETDATE())
And, to find the difference in weeks between two dates:
SELECT DATEDIFF ( WK , '1-1-2010', GETDATE())
BOL for DatePart: http://msdn.microsoft.com/en-us/library/ms174420.aspx
BOL for DateDiff http://msdn.microsoft.com/en-us/library/ms189794.aspx
-Jen

But be careful! This function does NOT return a week number as per ISO standard! (it will start with week 1 for Jan 1st regardless if this week has more than 4 days or not)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mike.sharp 49495 Valued Member Points: 59 More actions · Answer 8

SELECT DATEDIFF(dd,'2010-APR-01','2010-DEC-01') / 7

OR

SELECT DATEDIFF(wk,'2010-APR-01','2010-DEC-01')

Is this the question?

john.arnott SSChampion Points: 11882 More actions · Answer 9

Rem70Rem (1/5/2011)
Edited: Yesterday @ 10:38:18 AM by Rem70Rem

Uh, what was the question? 😉

Seriously, though, please restate the starting point so that others can learn from the discussion. That way folks wouldn't have to work out whatever the problem is that is beyond simply using a DateDiff() function.

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 10

Well I found out that datediff seems to do what I was looking for.

Thanks all for your help. Appreciated.

James Tran SSC Journeyman Points: 97 More actions · Answer 11

Something like this will return the number of weeks and remaining days + remaining work days.

CREATE FUNCTION [dbo].udfGetWeeksAndDays

(

@StartDate DATETIME

,@EndDate DATETIME

)

RETURNS

TABLE

AS

RETURN

(

with cte as (

SELECT

TotalDays = DATEDIFF(dd, @StartDate, @EndDate) + 1

,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7

,RemainDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1) % 7

)

select

*

,RemainWorkDays = case when RemainDays > 5 then 5 else RemainDays end

From cte

)

GO

MMartin1 One Orange Chip Points: 27764 More actions · Answer 12

Seems like the OP got his answer to this old thread.

Plus

DATEDIFF(wk, @StartDate, @EndDate)

Is cleaner and more readable, answers the one question posted here.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27764 More actions · Answer 13

James Tran (12/13/2016)
Something like this will return the number of weeks and remaining days + remaining work days.
CREATE FUNCTION [dbo].udfGetWeeksAndDays
(
@StartDate DATETIME
,@EndDate DATETIME
)
RETURNS
TABLE
AS
RETURN
(
with cte as (
SELECT
TotalDays = DATEDIFF(dd, @StartDate, @EndDate) + 1
,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
,RemainDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1) % 7
)
select
*
,RemainWorkDays = case when RemainDays > 5 then 5 else RemainDays end
From cte
)
GO

If you look at this portion of your code

,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7

it fails when the end date is less than the start date.

Compare these two results

declare @startDate date, @endDate date;

select @startDate = '20161212', @endDate = '20161205'

select

yourFormula= (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7

,myFormula = DATEDIFF(wk, @StartDate, @EndDate)

And see how your formula produces the wrong result.

----------------------------------------------------

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 14

MMartin1 (12/14/2016)
James Tran (12/13/2016)
Something like this will return the number of weeks and remaining days + remaining work days.
CREATE FUNCTION [dbo].udfGetWeeksAndDays
(
@StartDate DATETIME
,@EndDate DATETIME
)
RETURNS
TABLE
AS
RETURN
(
with cte as (
SELECT
TotalDays = DATEDIFF(dd, @StartDate, @EndDate) + 1
,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
,RemainDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1) % 7
)
select
*
,RemainWorkDays = case when RemainDays > 5 then 5 else RemainDays end
From cte
)
GO
If you look at this portion of your code
,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
it fails when the end date is less than the start date.
Compare these two results
declare @startDate date, @endDate date;
select @startDate = '20161212', @endDate = '20161205'
select
yourFormula= (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
,myFormula = DATEDIFF(wk, @StartDate, @EndDate)
And see how your formula produces the wrong result.

Eh, it all depends on the requirements.

Just using DATEDIFF(wk...) means that a start of 20161217 and an end of 20161218 will return 1 week, since it's just counting boundaries (which with DATEDIFF(wk...) is always Sunday, regardless of DATEFIRST settings).

That might be desired, or it might not be.

The desired logic and results would have to be spelled out precisely before picking a particular solution.

Cheers!