April 22, 2009 at 2:20 am
hi all,
i want query to show week range depending upon selection of date.so how to do that.I want to give any date selection to the user.and depend upon the selection of date user can see the report between the particular week range ag that report.how to do that.plo help it's urgent
thank u:-P
April 22, 2009 at 6:41 am
I'm not sure I understand your requirements. Can you include some data and expected results? If I enter April 22, 2009 (Wednesday) do you want the week to be Sunday, April 19 through Saturday, April 25?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 22, 2009 at 9:09 am
ya,exactly i want that.but user should select week number from particular year. i.e. week number is parameter.and from that week number i want to decide week date range including sunday.
thank u
April 22, 2009 at 9:34 am
You can get the week number from the datepart() function. I think if you are showing a range of dates, get the week numbers from the first and last date, and then display those as limits.
April 22, 2009 at 9:43 am
Okay, I am going to make a suggestion that I always make in these situations. Create a calendar table. That will simplify things greatly. Something like this will work (you'd need to define this as needed for your business needs):
CREATE TABLE dbo.Calendar
(
the_date SMALLDATETIME,
the_year INT,
the_quarter INT,
the_month INT,
the_week INT
)
SELECT
MIN(the_date) AS week_start,
MAX(the_date) AS week_end
FROM
dbo.Calendar AS C
WHERE
the_week = @week_of_year
If you can't do this, then something like this will work:
SELECT
DATEADD(DAY, - 1, dateadd(wk, datediff(wk, 0, DATEADD(week, DATEPART(week, GETDATE())-1, '1/1/2009')), 0)) AS week_start,
DATEADD(DAY, - 2, dateadd(wk, datediff(wk, 0, DATEADD(week, DATEPART(week, GETDATE()), '1/1/2009')), 0)) AS week_end
Check out this blog post for some explanation of the way I am using the date functions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply