Solved

How can I get all mondays on the last six months using SQL server query

Posted on 2008-06-13
2
969 Views
Last Modified: 2008-06-13
In SQL Server 2005, I would like to get all mondays in the last 6 months. The query should return me the dates on which all mondays in the last 6 months are happenig.

How can I create such a query?

Thanks
0
Comment
Question by:chaleastale
  • 2
2 Comments
 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
ID: 21780355
You can do this with a SP.  I have a it selecting them only and then commented out an insert statement if you want to put them in a table.

NOTE:  You have to set the date of the first monday you want to find and the last day you want to go to.  This will do them all from the start date to the end date.

If you need to dynamically find the first monday from the current date let me know.
DECLARE @WorkingDate as datetime
 
 
-- set to the first StartDate you want for the first Sunday
Set @WorkingDate = '6/16/2008'
 
-- do till the end date you wnat reached
while @WorkingDate > '1/6/2008'
        Begin 
                -- insert current working Monday date, and then add 6 to that to get the next saturday (ending dat)
                --Insert Into DatabaseTableName
                --(MondayDate)
                --Values(@WorkingDate, DATEADD (dd, 7, @WorkingDate))
 
				select @WorkingDate
 
                -- increment sunday date for next loop
                Set @WorkingDate = DATEADD (dd, -7, @WorkingDate)                
                --print @WorkingDate
        end

Open in new window

0
 
LVL 16

Expert Comment

by:brad2575
ID: 21780423
Here you go this is all combined.  It finds the first monday going forward from the current date

Then gets the past 6 months worth of mondays from that date.

--datepart = Sunday = 1, Saturday = 7
 
DECLARE @WorkingDate as datetime
DECLARE @FirstMondayDate as datetime
DECLARE @EndDate as datetime
DECLARE @DayOfWeekLookingFor as int
 
-- set to the first StartDate you want for the first Sunday
Set @WorkingDate = GetDate()
Set @FirstMondayDate = ''
 
-- for monday
Set @DayOfWeekLookingFor = 2 
 
-- do this to find the number of the days of week found 
while @FirstMondayDate = ''
	Begin 
		-- if day of week matches what we are looking for add one to counter
		IF datepart(weekday, @WorkingDate) = @DayOfWeekLookingFor
			Set @FirstMondayDate = @WorkingDate
		
		-- increment date by one day and loop again
		Set @WorkingDate = DATEADD (dd, 1, @WorkingDate)		
		
	end
 
-- set the enddate to 6 months from the first monday we found
Set @EndDate = DATEADD (mm, -6, GetDate())
 
-- set working date to the first monday date we found above for next loop
Set @WorkingDate = @FirstMondayDate
 
 
 
-- this loops through from the first day found above to the last one requested
-- do till the end date you wnat reached
while @WorkingDate > @EndDate
        Begin 
                -- insert current working Monday date, and then add 6 to that to get the next saturday (ending dat)
                --Insert Into DatabaseTableName
                --(MondayDate)
                --Values(@WorkingDate, DATEADD (dd, 7, @WorkingDate))
				
				-- just select the date
                select @WorkingDate
 
                -- increment sunday date for next loop
                Set @WorkingDate = DATEADD (dd, -7, @WorkingDate)                
                --print @WorkingDate
        end

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSIS how to COMPARE a data column from different servers? 6 107
Record open by another user 6 63
Strange msg in the SSMS pane 13 58
What is this datetime? 1 19
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question