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)