July 10, 2012 at 8:36 pm
Hello, I need some help from the experts to get the Start date for each week in a given month.
For example:
Month = June
Week Started:
06-01-2012 (Week1)
06-08-2012 (Week2)
06-015-2012 (Week3)
06-022-2012 (Week4)
Basically i need to create an SSRS report and needs to setup parameters like Month and Weeks.I research alot but didnt find any good resolution.:cool:
Thanks! Ad
July 10, 2012 at 9:32 pm
If the first week on the month always starts on day 1 on the month, this should do it:
declare @MonthStart datetime
-- Find first day of current month
set @MonthStart = dateadd(mm,datediff(mm,0,getdate()),0)
select
Week,
WeekStart = dateadd(dd,(Week-1)*7,@MonthStart)
from
( -- Week numbers
select Week = 1 union all select 2 union all
select 3 union all select 4 union all select 5
) a
where
-- Necessary to limit to 4 weeks for Feb in non-leap year
datepart(mm,dateadd(dd,(Week-1)*7,@MonthStart)) =
datepart(mm,@MonthStart)
Results:
Week WeekStart
----------- -----------------------
1 2012-07-01 00:00:00.000
2 2012-07-08 00:00:00.000
3 2012-07-15 00:00:00.000
4 2012-07-22 00:00:00.000
5 2012-07-29 00:00:00.000
(5 row(s) affected)
July 10, 2012 at 10:52 pm
Arman Khan (7/10/2012)
Hello, I need some help from the experts to get the Start date for each week in a given month.For example:
Month = June
Week Started:
06-01-2012 (Week1)
06-08-2012 (Week2)
06-015-2012 (Week3)
06-022-2012 (Week4)
Basically i need to create an SSRS report and needs to setup parameters like Month and Weeks.I research alot but didnt find any good resolution.:cool:
Thanks! Ad
Hi Arman,
Please post your exact requirement as the example posted by you is quite confusing as first in the month of JUN 2012 there were 5 weeks.
Secondly first week started on FRIDAY dated 01-JUN-2012 and second week started on SUNDAY 03-JUN-2012.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 11, 2012 at 7:18 am
rhythmk (7/10/2012)
Arman Khan (7/10/2012)
Hello, I need some help from the experts to get the Start date for each week in a given month.For example:
Month = June
Week Started:
06-01-2012 (Week1)
06-08-2012 (Week2)
06-015-2012 (Week3)
06-022-2012 (Week4)
Basically i need to create an SSRS report and needs to setup parameters like Month and Weeks.I research alot but didnt find any good resolution.:cool:
Thanks! Ad
Hi Arman,
Please post your exact requirement as the example posted by you is quite confusing as first in the month of JUN 2012 there were 5 weeks.
Secondly first week started on FRIDAY dated 01-JUN-2012 and second week started on SUNDAY 03-JUN-2012.
Try out this
DECLARE @a VARCHAR(20)
DECLARE @b-2 VARCHAR(20)
DECLARE @e INT
DECLARE @date DATETIME
DECLARE @date1 DATETIME
--Enter the any date here for which you need data
SET @date = '04-02-2012 00:00:00:000'
SELECT @a = DATENAME(dw,DATEADD(d,-DATEPART(dd,@date)+1,@date))
IF (@a = 'SUNDAY')
BEGIN
SET @e = 0
END
ELSE
BEGIN
SET @e = 1
END
--Use This block if you need from 1st of every month
IF(@a <> 'SUNDAY')
BEGIN
SELECT DATEADD(d,-DATEPART(dd,@date)+1,@date)[DATE],DATENAME(dw,DATEADD(d,-DATEPART(dd,@date)+1,@date))[DAY],@e [WEEK]
END
--Use This block if you need from 1st of every month
SELECT @date1= DATEADD(d,-DATEPART(dd,@date)+1,@date)
SELECT @date = DATEADD(d,-DATEPART(dd,@date)+1,@date)
IF @a = 'Sunday'
BEGIN
SELECT @date1 = DATEADD(d,0,@date)
END
ELSE IF @a = 'Monday'
BEGIN
SELECT @date1 = DATEADD(d,6,@date)
END
ELSE IF @a = 'Tuesday'
BEGIN
SELECT @date1 = DATEADD(d,5,@date)
END
ELSE IF @a = 'Wednesday'
BEGIN
SELECT @date1 = DATEADD(d,4,@date)
END
ELSE IF @a = 'Thursday'
BEGIN
SELECT @date1 = DATEADD(d,3,@date)
END
ELSE IF @a = 'Friday'
BEGIN
SELECT @date1 = DATEADD(d,2,@date)
END
ELSE IF @a = 'Saturday'
BEGIN
SELECT @date1 = DATEADD(d,1,@date)
END
SELECT @a = DATEPART(mm,@date)
SELECT @b-2 = DATEPART(mm,@date1)
WHILE (@a = @b-2)
BEGIN
SELECT @date1 [DATE],DATENAME(dw,@date1) [DAY],@e +1 [WEEK]
SET @date1 = DATEADD(d,7,@date1)
SELECT @a = DATEPART(mm,@date1)
SET @e = @e + 1
END
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 11, 2012 at 8:00 am
Thanks a lot of the reply guys!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply