I am new to the SQL , can anyone give me query for this scenario ,"I need to display the startdate and enddate of the week starting from today's date to next 8 weeks " . For example if i select today's date , it should show
Try this:
DECLARE @startDate DATETIME
DECLARE @currentDate DATETIME
DECLARE @numberOfWeeks INT
DECLARE @dates TABLE(
StartDate DateTime,
EndDate DateTime
)
SET @startDate = GETDATE()--'2012-01-01' -- Put whatever you want here
SET @numberOfWeeks = 8 -- Choose number of weeks here
SET @currentDate = @startDate
while @currentDate < dateadd(week, @numberOfWeeks, @startDate)
begin
INSERT INTO @Dates(StartDate, EndDate) VALUES (@currentDate, dateadd(day, 6, @currentDate))
set @currentDate = dateadd(day, 7, @currentDate);
end
SELECT * FROM @dates
This will give you something like this:
StartDate EndDate
21/03/2013 11:22:46 27/03/2013 11:22:46
28/03/2013 11:22:46 03/04/2013 11:22:46
04/04/2013 11:22:46 10/04/2013 11:22:46
11/04/2013 11:22:46 17/04/2013 11:22:46
18/04/2013 11:22:46 24/04/2013 11:22:46
25/04/2013 11:22:46 01/05/2013 11:22:46
02/05/2013 11:22:46 08/05/2013 11:22:46
09/05/2013 11:22:46 15/05/2013 11:22:46
Or you could tweak the final select if you don't want the time component, like this:
SELECT CONVERT(VARCHAR, StartDate, 103), CONVERT(VARCHAR, EndDate, 103) FROM @dates
You can have a look at this StackOverFlow post: Generate a resultset of incrementing dates in TSQL
or
The following should give you that result:
declare @dateIterator datetime = getdate();
declare @endDate datetime = dateadd(week, 8, getdate());
declare @dateRange varchar(max) = '';
while @dateIterator < @endDate
begin
set @dateIterator = dateadd(day, 1, @dateIterator);
set @dateRange = @dateRange + ' ' + convert(varchar(10), @dateIterator, 3)
end
select @dateRange
Hope this helps!
If you want to avoid iterating (for instance, if you need this data in a view), you can use the following technique:
;WITH w(weeknumber) AS
(SELECT 0
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7),
f(firstdayofweek) AS
(SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)),
o(offsetweekdate) AS
(SELECT DATEADD(ww,w.weeknumber,f.firstdayofweek) FROM w,f)
SELECT
DATEADD(d,0,o.offsetweekdate) AS firstdayofweek,
DATEADD(d,6,o.offsetweekdate) AS lastdayofweek
FROM o
Here, w generates a list of numbers from 0 to 7,f finds the first day of the week, and o combines these two to give the start and end dates of the week for the next 8 weeks.
The disadvantage of this is that to increase the number of weeks projected forwards, you'd need to add an extra line to the definition of w. This is because there is no built-in way to generate a range of values using TSQL without using loops.
Jeff Moden posted a very helpful range function elsewhere on StackOverflow which I like to use in these kind of scenarios.
CREATE FUNCTION [dbo].[Range](@startvalue integer,@endvalue integer)
RETURNS TABLE
AS
RETURN(
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT TOP (@endvalue-@startvalue+1) (N+@startvalue-1) AS Number
FROM cteTally
WHERE N <= (@endvalue-@startvalue+1))
This allows you to generate a range using a simple SELECT Number FROM RANGE(0,10). Once you've defined this function, the original code can be reduced to the following:
;WITH w(weeknumber) AS
(SELECT Number FROM Range(0,7)),
f(firstdayofweek) AS
(SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)),
o(offsetweekdate) AS
(SELECT DATEADD(ww,w.weeknumber,f.firstdayofweek) FROM w,f)
SELECT
DATEADD(d,0,o.offsetweekdate) AS firstdayofweek,
DATEADD(d,6,o.offsetweekdate) AS lastdayofweek
FROM o
It would then be a simple change to extend this to take a parameter for the number of weeks to project forwards.
