IRohitable | SQL Server: Get week start and end dates of any month

SQL Server: Get week start and end dates of any month

by Rohit 19. January 2013 13:38

Get week start dates of any month

DECLARE @startDate DATETIME
SET @StartDate = '20130101'
;WITH DayCounter(n) AS
( SELECT 0 UNION ALL SELECT n+1 FROM DayCounter WHERE n<30)
SELECT DISTINCT DateAdd(dd,7-(DATEPART(DW,@StartDate+n)-1),@StartDate+n) FROM DayCounter
WHERE MONTH(DateAdd(dd,7-(DATEPART(DW,@StartDate+n)-1),@StartDate+n)) = MONTH(@StartDate)

 

Get week end dates of any month

DECLARE @StartDate DATETIME
SET @StartDate = '20130101'
;WITH DayCounter(n) AS
( SELECT 0 UNION ALL SELECT n+1 FROM DayCounter WHERE n<30)
SELECT DISTINCT DateAdd(dd,7-(DATEPART(DW,@StartDate+n)),@StartDate+n) FROM DayCounter
WHERE MONTH(DateAdd(dd,7-(DATEPART(DW,@StartDate+n)-1),@StartDate+n)) = MONTH(@StartDate)


Tags: , ,

SQL Server | Technology

Comments (1) -

kirtya
10/8/2015 2:38:35 PM #

i have a table with all the dates, meaning, each day and to which there is a count of some incidents occurring each day, now i want to create a bucket which has weekstart date and weekend date with the the total number of incidents as sum in front of weekstart date, and then like this only for the rest of the dates.
please can anyone provide a solution

Add comment

biuquote
  • Comment
  • Preview
Loading

About Rohit Prakash

Software Craftsman and Technology Enthusiast (not a Guru).

Technical Reviewer of a book on open-source programming IDE.

My day job keeps me engaged with Microsoft Technologies (ASP.NET, C# and SQL Server) hence most of the posts are related to these technologies. I, however, love to play with other open-source technologies. I also have interest in IT Security and therefore you will find posts on Malware Analysis, Cryptography and Anti-Virus programs.

Few people have contacted me for Guest Posts. You will find these posts as well.

 

You can reach me at:

rohit [at] irohitable.com

-----------------------------------------

LinkedIn:

in.linkedin.com/in/rohitpkhare

Twitter:

@BuzzRohit

-----------------------------------------
Google+

Search with this name: Rohit Prakash

Month List

Protected by Copyscape Web Plagiarism Software