Query to find the start day of the week as Monday

• var05

  Hall of Fame

  Points: 3011

  More actions

  December 4, 2012 at 7:55 am

  #267100

  Hi All,

  How to specify the start day of the week as Monday for the below records

  I have tow fields Record and Load date... Want to have another additional Col start date of the week

  Results as below:

  Record Loaddate Start date of the week (As Monday)

  A 03/12/2012 03/12/2012

  B 04/12/2012 03/12/2012

  C 05/12/2012 03/12/2012

  D 09/12/2012 03/12/2012

  I used the below query

  select CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, '12/04/2012'), 0),101) AS Week

  The above query works fine and fetches the start day of the week As monday only if the day is between Monday - Saturday.

  But say for Sunday '12/09/2012', the start day of the week is considered as the subsequent Monday 12/10/2012... But for the Sunday 12/09/2012, I wanted the start date of the week as '12/03/2012'

  Any help on this?

  Thanks

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  December 4, 2012 at 8:21 am

  #1565358

  Sounds like you should look into a calendar table. Check out this article. http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• var05

  Hall of Fame

  Points: 3011

  More actions

  December 4, 2012 at 8:40 am

  #1565379

  Thanks WIll take a look at it:)

• arnipetursson

  SSCertifiable

  Points: 6557

  More actions

  December 4, 2012 at 9:10 am

  #1565386

  set DATEFIRST 1

  declare @dt datetime

  select @dt = '12/09/2012'

  select previousMonday = dateadd(dd,(-1)*(datepart(dw,@dt)-1),@dt)

  set DATEFIRST 7

• var05

  Hall of Fame

  Points: 3011

  More actions

  December 4, 2012 at 9:23 am

  #1565391

  Thanks for the reply.

  I used the below case statement in my Select statement to get the starting day as Monday:

  (CASE WHEN datename(dw,date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, date), - 7), 103) END)

  Thanks all!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2012 at 10:04 am

  #1565414

  Compare:

  DECLARE @date DATE = '20121202';

  SELECT

  CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END

  go

  DECLARE @date DATE = '20121203';

  SELECT

  CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END

  go

  DECLARE @date DATE = '20121202';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

  DECLARE @date DATE = '20121203';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  December 4, 2012 at 10:54 am

  #1565435

  How about:

  CASE

  WHEN DATEPART(dw, THE_DATE) = 1 THEN DATEADD(dd, -6, THE_DATE)

  ELSE DATEADD(dd, 0 - (DATEPART(dw, THE_DATE) - 2), THE_DATE)

  END

  where THE_DATE is assumed to be the date field...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2012 at 11:04 am

  #1565440

  sgmunson (12/4/2012)

  ---

  How about:

  CASE

  WHEN DATEPART(dw, THE_DATE) = 1 THEN DATEADD(dd, -6, THE_DATE)

  ELSE DATEADD(dd, 0 - (DATEPART(dw, THE_DATE) - 2), THE_DATE)

  END

  where THE_DATE is assumed to be the date field...

  No conditional logic required:

  DECLARE @date DATE = '20121202';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

  DECLARE @date DATE = '20121203';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  December 4, 2012 at 11:07 am

  #1565443

  select

  a.*,

  Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

  Results:

  Date Monday

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

  2012-12-02 13:05:22.770 2012-11-26 00:00:00.000

  2012-12-03 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-04 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-05 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-06 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-07 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-08 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-09 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-10 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-11 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-12 13:05:22.803 2012-12-10 00:00:00.000

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2012 at 11:17 am

  #1565446

  Michael Valentine Jones (12/4/2012)

  ---

  select

  a.*,

  Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

  Results:

  Date Monday

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

  2012-12-02 13:05:22.770 2012-11-26 00:00:00.000

  2012-12-03 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-04 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-05 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-06 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-07 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-08 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-09 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-10 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-11 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-12 13:05:22.803 2012-12-10 00:00:00.000

  Or:

  select

  a.*,

  Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  December 4, 2012 at 11:18 am

  #1565447

  Lynn Pettis (12/4/2012)

  ---

  Compare:

  DECLARE @date DATE = '20121202';

  SELECT

  CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END

  go

  DECLARE @date DATE = '20121203';

  SELECT

  CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END

  go

  DECLARE @date DATE = '20121202';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

  DECLARE @date DATE = '20121203';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

  Yup, you have it right. I didn't get to see your post before I posted my answer. I'd sure like to understand a bit more about why that works. It's using the number of weeks since date zero through yesterday, then re-adding the same number of weeks to date zero, which suggests that perhaps the week is defined based on Monday ? Or is it Sunday, and thus why yesterday is used instead of today?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2012 at 11:22 am

  #1565449

  sgmunson (12/4/2012)

  ---

  Lynn Pettis (12/4/2012)

  ---

  Compare:

  DECLARE @date DATE = '20121202';

  SELECT

  CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END

  go

  DECLARE @date DATE = '20121203';

  SELECT

  CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),

  103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END

  go

  DECLARE @date DATE = '20121202';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

  DECLARE @date DATE = '20121203';

  select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);

  GO

  Yup, you have it right. I didn't get to see your post before I posted my answer. I'd sure like to understand a bit more about why that works. It's using the number of weeks since date zero through yesterday, then re-adding the same number of weeks to date zero, which suggests that perhaps the week is defined based on Monday ? Or is it Sunday, and thus why yesterday is used instead of today?

  Will explain tonight.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  December 4, 2012 at 12:47 pm

  #1565473

  Lynn Pettis (12/4/2012)

  ---

  Michael Valentine Jones (12/4/2012)

  ---

  select

  a.*,

  Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

  Results:

  Date Monday

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

  2012-12-02 13:05:22.770 2012-11-26 00:00:00.000

  2012-12-03 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-04 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-05 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-06 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-07 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-08 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-09 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-10 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-11 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-12 13:05:22.803 2012-12-10 00:00:00.000

  Or:

  select

  a.*,

  Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

  Not completely the same, though:

  select

  a.*,

  Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  ( select [Date]= convert(datetime,'17530101') ) a

  select

  a.*,

  Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

  from

  ( select [Date]= convert(datetime,'17530101') )

  Results:

  Date Monday

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

  1753-01-01 00:00:00.000 1753-01-01 00:00:00.000

  (1 row(s) affected)

  Date Monday

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

  Msg 517, Level 16, State 1, Line 7

  Adding a value to a 'datetime' column caused an overflow.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2012 at 4:09 pm

  #1565526

  Michael Valentine Jones (12/4/2012)

  ---

  Lynn Pettis (12/4/2012)

  ---

  Michael Valentine Jones (12/4/2012)

  ---

  select

  a.*,

  Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

  Results:

  Date Monday

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

  2012-12-02 13:05:22.770 2012-11-26 00:00:00.000

  2012-12-03 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-04 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-05 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-06 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-07 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-08 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-09 13:05:22.803 2012-12-03 00:00:00.000

  2012-12-10 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-11 13:05:22.803 2012-12-10 00:00:00.000

  2012-12-12 13:05:22.803 2012-12-10 00:00:00.000

  Or:

  select

  a.*,

  Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

  from

  ( -- Test Data

  select [Date]= getdate()-2union all

  select [Date]= getdate()-1union all

  select [Date]= getdate()union all

  select [Date]= getdate()+1union all

  select [Date]= getdate()+2union all

  select [Date]= getdate()+3union all

  select [Date]= getdate()+4union all

  select [Date]= getdate()+5union all

  select [Date]= getdate()+6union all

  select [Date]= getdate()+7union all

  select [Date]= getdate()+8

  ) a

  order by

  a.[Date]

  Not completely the same, though:

  select

  a.*,

  Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  ( select [Date]= convert(datetime,'17530101') ) a

  select

  a.*,

  Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

  from

  ( select [Date]= convert(datetime,'17530101') )

  Results:

  Date Monday

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

  1753-01-01 00:00:00.000 1753-01-01 00:00:00.000

  (1 row(s) affected)

  Date Monday

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

  Msg 517, Level 16, State 1, Line 7

  Adding a value to a 'datetime' column caused an overflow.

  Not saying that there aren't, but most database applications don't nned to go that far back in time. I'd call that an edge case.

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  December 4, 2012 at 5:08 pm

  #1565532

  Then substitute a 0 for Michael's -53690. 😀

  That, not withstanding, it's always the "edge cases" that cause panic stricken calls at 3 in the morning.

  --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)