How to find next 6 months dates basing on..

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

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

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

gvmk27
Starting Member

44 Posts

Posted - 2015-02-06 : 15:05:20

THank you James.

gvmk27
Starting Member

44 Posts

Posted - 2015-02-06 : 15:06:05

Thank you SwePeso!

quote:
Originally posted by gvmk27

THank you James.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources