Complete weeks

Question

Post reply

Complete weeks

sharonsql2013

SSCertifiable

Points: 5908
More actions
April 30, 2015 at 2:49 pm

#317906

I need to divide a number by the number of full weeks based on today's date.
Say today is April 30 which is Week 18th but since its the middle of the week i would like to get 17. IS that possible?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63436 More actions · Answer 1

full weeks since when? the first of the year?

Maybe Lynn Pettis' article will help:

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

Bill Talada SSChampion Points: 11954 More actions · Answer 2

DECLARE @dt datetime;

SET @dt = '20150102';

SELECT DATEPART(week,@dt) - CASE WHEN DATEPART(WEEKDAY,@dt) < 7 THEN 1 ELSE 0 end;

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 3

sharonsql2013

SSCertifiable

Points: 5908

April 30, 2015 at 3:20 pm

#1794862

That helps..Thanks a lot

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

sharonsql2013 (4/30/2015)
That helps..Thanks a lot

Actually, it doesn't. The WEEK datepart doesn't count whole weeks.

--===== Note that the dates below occur on an adjacent Wednesday and Thursday respectively.

SELECT DATEPART(WEEK,'2014-12-31'), DATEPART(WEEK,'2015-01-01');

You also haven't defined when a week starts nor when a year starts and it can easily be different from instance to instance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 5

I want the start of the week from 04-12-2015 thru 4-18-2015. (Sat thru Sun)

pietlinden SSC Guru Points: 63436 More actions · Answer 6

pietlinden

SSC Guru

Points: 63436

April 30, 2015 at 5:26 pm

#1794879

What if you use a Calendar table?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

sharonsql2013 (4/30/2015)
I want the start of the week from 04-12-2015 thru 4-18-2015. (Sat thru Sun)

What should the start of the week for the 1st of January 2015 be and should it be considered as 2014 or 2015? Also, I think you meant Sunday through Saturday? If that's true, then having a value of DATEFIRST =7 for the server and using Bill's WEEK calculations will work fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

sharonsql2013 (4/30/2015)
I need to divide a number by the number of full weeks based on today's date.
Say today is April 30 which is Week 18th but since its the middle of the week i would like to get 17. IS that possible?

(Almost) everything is possible, consider this code, should be enough to get you passed this hurdle.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @MY_DATE DATE = CONVERT(DATE,'20150430',112);

DECLARE @ZERO_DATE DATE = CONVERT(DATE,'19000101',112);

SELECT

/* Day of the week, 1 = Monday*/

(DATEDIFF(DAY,@ZERO_DATE,@MY_DATE) % 7) + 1

/* Number of weeks since Monday 1900-01-01 */

,DATEDIFF(DAY,@ZERO_DATE,@MY_DATE) / 7.0

/* First day of this year */

,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)

/* Number of days since Monday 1900-01-01 until the beginning of this year */

,DATEDIFF(DAY,@ZERO_DATE,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)) / 7.0

/* Day of the week at the beginning of the year, 1 = Monday*/

,(DATEDIFF(DAY,@ZERO_DATE,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)) % 7) + 1

/* Days passed since beginning of the year */

,(DATEDIFF(DAY,@ZERO_DATE,@MY_DATE) / 7.0)

- (DATEDIFF(DAY,@ZERO_DATE,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)) / 7.0)

/* Day of the year */

,DATEPART(DAYOFYEAR,@MY_DATE)

/* Week of the year */

,DATEPART(WEEK,@MY_DATE)

/* ISO_WEEK of the year */

,DATEPART(ISO_WEEK,@MY_DATE)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

Eirikur Eiriksson (4/30/2015)
sharonsql2013 (4/30/2015)
I need to divide a number by the number of full weeks based on today's date.
Say today is April 30 which is Week 18th but since its the middle of the week i would like to get 17. IS that possible?
(Almost) everything is possible, consider this code, should be enough to get you passed this hurdle.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @MY_DATE DATE = CONVERT(DATE,'20150430',112);
DECLARE @ZERO_DATE DATE = CONVERT(DATE,'19000101',112);
SELECT
/* Day of the week, 1 = Monday*/
(DATEDIFF(DAY,@ZERO_DATE,@MY_DATE) % 7) + 1
/* Number of weeks since Monday 1900-01-01 */
,DATEDIFF(DAY,@ZERO_DATE,@MY_DATE) / 7.0
/* First day of this year */
,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)
/* Number of days since Monday 1900-01-01 until the beginning of this year */
,DATEDIFF(DAY,@ZERO_DATE,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)) / 7.0
/* Day of the week at the beginning of the year, 1 = Monday*/
,(DATEDIFF(DAY,@ZERO_DATE,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)) % 7) + 1
/* Days passed since beginning of the year */
,(DATEDIFF(DAY,@ZERO_DATE,@MY_DATE) / 7.0)
- (DATEDIFF(DAY,@ZERO_DATE,DATEADD(YEAR,DATEDIFF(YEAR,@ZERO_DATE,@MY_DATE),@ZERO_DATE)) / 7.0)
/* Day of the year */
,DATEPART(DAYOFYEAR,@MY_DATE)
/* Week of the year */
,DATEPART(WEEK,@MY_DATE)
/* ISO_WEEK of the year */
,DATEPART(ISO_WEEK,@MY_DATE)

That's all true but, except for ISO_WEEK, I'd like to know what the OP wants done for the first and last weeks of any given year, which are partial weeks 6 out of 7 times.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)