How to find first and last date of week and month for any given date

Question

Post reply

How to find first and last date of week and month for any given date

jarid.lawson

SSCrazy

Points: 2953
More actions
December 18, 2012 at 1:30 pm

#286625

This is another fun tool that I just made for one of my other developers for his C# report generator. In case anyone needs to see a way it can be done.
I'm also curious if someone else has a simpler way to do this.
Declare @TestDate Varchar(20)
,@StartOfMonth Varchar(20)
,@EndOfMonth Varchar(20)
,@FirstDayOfWeek_Sunday Varchar(20)
,@LastDayOfWeek_Saturday Varchar(20)
Set @TestDate = '2/15/2012'
Set @StartOfMonth = Cast(DatePart(mm,@TestDate) As Varchar)
+ '/1/'
+ Cast(DatePart(yyyy,@TestDate) As Varchar)
Set @EndOfMonth = Cast(DatePart(mm,@TestDate) As Varchar)
+ '/'
+ Case
When DatePart(mm,@TestDate) In
(1,3,5,7,8,10,12)
Then '31'
When DatePart(mm,@TestDate) In
(4,6,9,11)
Then '30'
When DatePart(mm,@TestDate) = 2
And DatePart(yyyy,@TestDate) % 4 = 0
Then '29'
When DatePart(mm,@TestDate) = 2
And DatePart(yyyy,@TestDate) % 4 <> 0
Then '28'
End
+ '/'
+ Cast(DatePart(yyyy,@TestDate) As Varchar)
Set @FirstDayOfWeek_Sunday = Convert(Varchar,Convert(DateTime,(DateAdd(dd,((DatePart(dw,@TestDate) * -1) + 1),@TestDate))),101)
Set @LastDayOfWeek_Saturday = Convert(Varchar,Convert(DateTime,(DateAdd(dd,((DatePart(dw,@TestDate) * -1) + 7),@TestDate))),101)
Select @TestDate As TestDate
,@StartOfMonth As StartOfMonth
,@EndOfMonth As EndOfMonth
,@FirstDayOfWeek_Sunday As FirstDayOfWeek_Sunday
,@LastDayOfWeek_Saturday As LastDayOfWeek_Saturday
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Like this:

SELECT GETDATE(),

DATEADD(week, DATEDIFF(week, '19050101', GETDATE()), '19050101') AS StartOfWeek,

DATEADD(month, DATEDIFF(month, '19050101', GETDATE()), '19050101') AS StartOfMonth,

DATEADD(day, -1, DATEADD(month, DATEDIFF(month, '19050101', GETDATE())+1, '19050101')) AS EndOfMonth

I picked 1 Jan 1905 for this because it is both the start of a year, the start of a month, and a Sunday (start of week).

You can pick a different 1 Jan if you need a different start-of-week than Sunday.

This kind of query is fast, efficient, simple, and easy to modify for different uses/needs.

Edit: You can, of course, use a variable with a different date than GetDate(). I just use that for this example.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Take a quick look at Lynn's article about some common date lookups.

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jarid.lawson SSCrazy Points: 2953 More actions · Answer 3

I stand in awe GSquared. That was most impressive.

I tweaked your code a touch, because it need the end of week, and the end of month went a little odd on a couple of runs (it came back with 1/1/1905).

Anyway, here is my tweaked version (mainly for an easy for me to find location). I also sent it on to my C# developer, and he was very impressed as well.

Declare @TestDate Varchar(20)

,@StartOfWeek Varchar(20)

,@EndOfWeek Varchar(20)

,@StartOfMonth Varchar(20)

,@EndOfMonth Varchar(20)

Set @TestDate = '2/24/2012'

Set @StartOfWeek = Convert(Varchar,DateAdd(Week,DateDiff(Week,'1/1/1905',@TestDate),'1/1/1905'),101)

Set @EndOfWeek = Convert(Varchar,DateAdd(Day, - 1,DateAdd(Week,DateDiff(Week,'1/1/1905',@TestDate) + 1,'1/1/1905')),101)

Set @StartOfMonth = Convert(Varchar,DateAdd(Month,DateDiff(Month,'1/1/1905',@TestDate),'1/1/1905'),101)

Set @EndOfMonth = Convert(Varchar,DateAdd(Month, + 1,DateAdd(Day, - 1,DateAdd(Month,DateDiff(Month,'1/1/1905',@TestDate),'1/1/1905'))),101)

Select @TestDate As TestDate

,@StartOfWeek As StartOfWeek

,@EndOfWeek As EndOfWeek

,@StartOfMonth As StartOfMonth

,@EndOfMonth As EndOfMonth

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 4

Your modification to end-of-month looks like it won't work correctly in all cases. Try a March date in there, see what you get.