display data between two dates week by week

Question

Post reply

display data between two dates week by week

bmr270

Default port

Points: 1477
More actions
April 18, 2009 at 8:54 am

#133188

I have to display data between two dates week by week. It should display Monday to Sunday.
Example
TableA
SnoSnameDate
1A04/01/09
2B04/02/09
3C04/03/09
4D04/04/09
5E04/05/09
6F04/06/09
7G04/07/09
8H04/08/09
9I04/09/09
10J04/010/09
11K04/011/09
I have to pass two variables to stored procedure. They are @startdate and @enddate.
Exec Stprocedure ‘04/01/09’ ‘04/11/09’
It should display
1st week Details
SnoSnameDate
1A04/01/09
2B04/02/09
3C04/03/09
4D04/04/09
5E04/05/09
2nd week Details
SnoSnameDate
6F04/06/09
7G04/07/09
8H04/08/09
9I04/09/09
10J04/010/09
11K04/011/09
Because this month started with Wednesday, that’s why it should display wed to sun for 1st week and Monday to Sunday for 2nd week.
It should display for all the weeks between two dates.

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

You can use DATEPART to get the weekday and the week:

DECLARE @t TABLE (Sno INT, Sname VARCHAR(10), Date DATETIME)

INSERT INTO @T

SELECT '1', 'A', '04/01/09'

UNION ALL SELECT '2', 'B', '04/02/09'

UNION ALL SELECT '3', 'C', '04/03/09'

UNION ALL SELECT '4', 'D', '04/04/09'

UNION ALL SELECT '5', 'E', '04/05/09'

UNION ALL SELECT '6', 'F', '04/06/09'

UNION ALL SELECT '7', 'G', '04/07/09'

UNION ALL SELECT '8', 'H', '04/08/09'

UNION ALL SELECT '9', 'I', '04/09/09'

UNION ALL SELECT '10', 'J', '04/010/09'

UNION ALL SELECT '11', 'K', '04/011/09'

DECLARE @from DATETIME

DECLARE @to DATETIME

SELECT @from = '04/01/09', @to = '04/11/09'

SELECT

'Week: ' +

CASE

WHEN DATEPART(WEEKDAY, Date) = 1

THEN CONVERT(VARCHAR(10), DATEPART(WEEK, Date) - 1)

ELSE CONVERT(VARCHAR(10), DATEPART(WEEK, Date))

END WeekNo,

*

FROM @t

ORDER BY WeekNo, Date

Greets

Flo

Paul White SSC Guru Points: 150468 More actions · Answer 2

Just a quick addition:

If you use DATEPART with weekday, make sure DATEFIRST is SET appropriately.

It is possible to write the comparison generically for any DATEFIRST by using @@DATEFIRST, but I can't immediately find the code for it - it involves some work with modulo though, if that helps...

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi