Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to find next 6 months dates basing on..

Author  Topic 

gvmk27
Starting Member

44 Posts

Posted - 2015-01-28 : 16:23:46
Hi

I need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.

I have a table called DayOfWeek and the records are

1 Sun
2 Mon
3 Tue
4 Wed
5 Thu
6 Fri
7 Sat

so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months

And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc

Any help is highly appreciated.

Thank you
MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-28 : 17:00:06
First create a calendar table - e.g. like this:
CREATE TABLE dbo.Calendar([Date] DATE);
GO
Now fill it with the date range you might need. If you plan to have your application running for several years, fill in all those dates. E.g.
;WITH cte AS
(
SELECT GETDATE() AS [Date]
UNION ALL
SELECT DATEADD(dd,1,[Date]) FROM cte
WHERE [Date] < '20161231'
)
INSERT INTO dbo.Calendar SELECT [Date] FROM cte
OPTION (MAXRECURSION 0 );
GO
Now query against that table like so:
DECLARE @dayofWeek INT = 1;

SELECT [Date]
FROM dbo.Calendar
WHERE
DATEDIFF(dd,'18991231',[Date])%7 = @dayofWeek-1 -- pick only the correct day of the week
AND DATEDIFF(dd,GETDATE(),[Date]) <= 180; -- limit to the number of days you want
Go to Top of Page

gvmk27
Starting Member

44 Posts

Posted - 2015-02-05 : 16:12:09
Thank you James. I don't have scope to create a table and refresh the dates every time. But really appreciate your reply. I could able to achieve as below

DECLARE @DayOfWeek INT = 4
DECLARE @D DATETIME
DECLARE @result DATETIME
declare @endDate DATETIME
set @endDate = dateadd(DAY,180,GETDATE())

SET @D = GETDATE()

SELECT @result = DateAdd(day, (@DayOfWeek - DatePart(WEEKDAY,
DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)))%7,
DateAdd(Month, DateDiff(Month, 0, @D), 0))

BEGIN
WHILE (@result < @endDate)
BEGIN
IF (GETDATE() > dateadd(DAY,1,@result))
BEGIN
set @result = dateadd(WEEK,1,@result)
END
Print @result
set @result = dateadd(WEEK,1,@result)
END
END
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-05 : 16:23:46
[code]DECLARE @Weekday TINYINT = 2;

WITH cteDates(theDate)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, DATEADD(DAY, @Weekday, '18991230'))

UNION ALL

SELECT DATEADD(DAY, 7, theDate)
FROM cteDates
WHERE theDate < DATEADD(MONTH, 6, GETDATE())
)
SELECT *
FROM cteDates
WHERE theDate >= CAST(GETDATE() AS DATE)
AND theDate <= DATEADD(MONTH, 6, CAST(GETDATE() AS DATE));[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

gvmk27
Starting Member

44 Posts

Posted - 2015-02-06 : 15:05:20
THank you James.
Go to Top of Page

gvmk27
Starting Member

44 Posts

Posted - 2015-02-06 : 15:06:05
Thank you SwePeso!

quote:
Originally posted by gvmk27

THank you James.

Go to Top of Page
   

- Advertisement -