Solved

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

Posted on 2008-06-13
2
926 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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now