DateDiff Rounding up weeks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

DateDiff Rounding up weeks

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-02-23 : 04:58:27

I want to get the number of weeks between two dates and round up that number up.

If I do:

DATEDIFF(week, 01-02-2012, 11-02-2012)
I will get the result 1 as it rounds to the nearest week. What I want is the result to always round up. So in this case the result would be 2.

Anyone please!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 05:41:23

this?



DECLARE @date1 DATE = '20120301'
DECLARE @date2 DATE = '20120310'

SELECT CEILING(DATEDIFF(DAY,@date1, @date2) / 7.0)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-02-23 : 07:12:29

Hi Charlie,

Many thanks for that.

The only thing is after testing I find that is I enter the dates

DECLARE @date1 DATE = '20120201'
DECLARE @date2 DATE = '20120208'

SELECT CEILING(DATEDIFF(DAY,@date1, @date2) / 7.0)

I get a result of 1 which should be 2. Because that calculates it as 8-1 = 7. Although those date actually mean a new week has started so I would want to record that as 2 weeks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 07:21:24

change the 7.0 to a 6.0 then.

Or did you actually want to split on some sort of calender weeks?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-02-23 : 07:27:06

Thanks Charlie,

Yes it would need to be on calender weeks for as soon as we hit 42 days that would then result in 7 weeks and not 6 weeks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 07:28:55

then the simplest thing to do is to make up a calender table with the week of the year as a column for each date.

Then join to the calender table for each date and take the difference in weekNo.

There are many, many articles on-line for calender tables.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-02-23 : 07:31:06

I did think I could just add a day to the end date

DECLARE @date1 DATE = '20120201'
DECLARE @date2 DATE = '20120208'

SELECT CEILING(DATEDIFF(DAY,@date1, @date2 + 1) / 7.0)

I can't see why this would not work? Correct me if I am wrong.

Many thanks, really appreciate your help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 09:29:16

OK. sorry I was confused by what you were saying. if it suits your requirement then awesome.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-02-23 : 10:17:19

Thats ok I have a knack of confusing myself, now I confuse other I have excelled.

Many thanks for your help Charlie, I am on my way!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources