SQL Server Performance Forum – Threads Archive
find the first and last date of the week
Hi Guys, How to find first date and last date of the week in the given two dates? Please help me Thanks in advance.lkarthik
what are two dates you gave? can you just write the input you gave? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Hi Table structure look like Date count
‘2006-03-01 00:00:00.000’ 5
‘2006-03-02 00:00:00.000’ 3
‘2006-03-03 00:00:00.000’ 7
‘2006-03-04 00:00:00.000’ 9
‘2006-03-05 00:00:00.000’ 5
‘2006-03-06 00:00:00.000’ 2
‘2006-03-07 00:00:00.000’ 2
‘2006-04-01 00:00:00.000’ 2
@Startdate >= ‘2006-03-01 00:00:00.000’
@Enddate < ‘2006-04-01 00:00:00.000’ The Report should be Date Countof Msgs
1st Week 50
2nd Week 80 lkarthik
You can use the DATEPART(week, <column><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /> syntax to find the week number, and group on that.
[email protected] table
(
[Date] datetime,
[Count]int
)
insert into @table
select ‘2006-03-01 00:00:00.000’, 5union all
select ‘2006-03-02 00:00:00.000’, 3union all
select ‘2006-03-03 00:00:00.000’, 7union all
select ‘2006-03-04 00:00:00.000’, 9union all
select ‘2006-03-05 00:00:00.000’, 5union all
select ‘2006-03-06 00:00:00.000’, 2union all
select ‘2006-03-07 00:00:00.000’, 2union all
select ‘2006-04-01 00:00:00.000’, 2 [email protected],
@Enddatedatetime
[email protected] = ‘2006-03-01’,
@Enddate = ‘2006-04-01’ select(datepart(week, [Date]) – datepart(week, @Startdate) + 1) as [Date],
sum([Count]) as [Count of Msgs]
[email protected]
whereDate>= @Startdate
andDate< @Enddate
group by datepart(week, [Date]) – datepart(week, @Startdate) + 1
KH
SELECT datepart(ww,adate) [Week Number]
,sum(bcount) [Count]
FROM TableName
WHERE datepart(ww, adate) = (datepart(ww, getdate()) – 1)
GROUP BY datepart(ww,adate)
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
substitute Getdate() to your date, you will get the first date of the week: select dateadd(d, 0, datediff(d,0 ,DATEADD(d, -DATEPART(dw, getdate()) + 1, getdatE())))
here is last date of the week: select dateadd(d, 0, datediff(d,0 ,DATEADD(d, 7-DATEPART(dw, getdate()), getdatE())))
p/s: First day of the week is Sunday and last day of the week is Saturday
]]>