Start Date

Question

Start Date

sharonsql2013

SSCertifiable

Points: 5908
More actions
May 4, 2015 at 2:16 pm

#307711

I am trying to get Start date for this week and the coming 3 weeks.
For this week this is what I have come up with :
Select DATEADD(dd, -(DATEPART(dw, '2015-05-06 15:10:37.183')-1), '2015-05-06 15:10:37.183') [WeekStart]
How can I get the start date for the next 3 weeks?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sharonsql2013 (5/4/2015)
I am trying to get Start date for this week and the coming 3 weeks.
For this week this is what I have come up with :
Select DATEADD(dd, -(DATEPART(dw, '2015-05-06 15:10:37.183')-1), '2015-05-06 15:10:37.183') [WeekStart]
How can I get the start date for the next 3 weeks?

What is StartDate? Is it Monday of each week? What do you want returned for Sunday's date?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

And to get you started, here is what I would do on the fly:

declare @ThisDate datetime;

set @ThisDate = getdate();

with ThreeWeek as (select rn from (values (0),(1),(2))dt(rn))

select dateadd(wk, datediff(wk, 0, @ThisDate) + rn, -1)

from ThreeWeek;

Also, check out this blog article: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 3

Monday So, Need to see May 4 , 2015 , May 11, 2015 , May 18,2015

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

sharonsql2013 (5/4/2015)
Monday So, Need to see May 4 , 2015 , May 11, 2015 , May 18,2015

So this will work:

declare @ThisDate datetime;

set @ThisDate = getdate();

with ThreeWeek as (select rn from (values (0),(1),(2))dt(rn))

select dateadd(wk, datediff(wk, 0, @ThisDate) + rn, 0)

from ThreeWeek;

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 5

Thanks. Tweaked it a bit to get different columns

declare @ThisDate datetime;

set @ThisDate = GETDATE();

with ThreeWeek as (select rn from (values (0),(1),(2))dt(rn))

select distinct dateadd(wk, datediff(wk, 0, @ThisDate) + (0), 0) FirstWeek

,dateadd(wk, datediff(wk, 0, @ThisDate) + (1), 0) Secondweek

,dateadd(wk, datediff(wk, 0, @ThisDate) + (2), 0) ThirdWeek

from ThreeWeek;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

sharonsql2013 (5/4/2015)
Thanks. Tweaked it a bit to get different columns
declare @ThisDate datetime;
set @ThisDate = GETDATE();
with ThreeWeek as (select rn from (values (0),(1),(2))dt(rn))
select distinct dateadd(wk, datediff(wk, 0, @ThisDate) + (0), 0) FirstWeek
,dateadd(wk, datediff(wk, 0, @ThisDate) + (1), 0) Secondweek
,dateadd(wk, datediff(wk, 0, @ThisDate) + (2), 0) ThirdWeek
from ThreeWeek;

You could drop the CTE as well:

declare @ThisDate datetime;

set @ThisDate = GETDATE();

select

dateadd(wk, datediff(wk, 0, @ThisDate) + (0), 0) FirstWeek

,dateadd(wk, datediff(wk, 0, @ThisDate) + (1), 0) Secondweek

,dateadd(wk, datediff(wk, 0, @ThisDate) + (2), 0) ThirdWeek;