April 18, 2009 at 8:54 am
I have to display data between two dates week by week. It should display Monday to Sunday.
Example
TableA
Sno Sname Date
1 A 04/01/09
2 B 04/02/09
3 C 04/03/09
4 D 04/04/09
5 E 04/05/09
6 F 04/06/09
7 G 04/07/09
8 H 04/08/09
9 I 04/09/09
10 J 04/010/09
11 K 04/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
Sno Sname Date
1 A 04/01/09
2 B 04/02/09
3 C 04/03/09
4 D 04/04/09
5 E 04/05/09
2nd week Details
Sno Sname Date
6 F 04/06/09
7 G 04/07/09
8 H 04/08/09
9 I 04/09/09
10 J 04/010/09
11 K 04/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.
April 18, 2009 at 3:22 pm
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
How to Post Data/Code to get the best Help How to Post Performance Problems
April 19, 2009 at 7:42 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 7:17 am
Thank you for all
April 20, 2009 at 8:36 am
you can also try
SELECT datepart(wk,Date) as 'WeekNo', Date
FROM TableA
where Date between @startDate and @endDate
ORDER BY WeekNo, Date
April 20, 2009 at 9:49 am
I got the solution. Thanks for reply
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply