How to get start day and end day in month based on date entry?

Question

How to get start day and end day in month based on date entry?

mtraore08

Right there with Babe

Points: 771
More actions
January 7, 2010 at 2:03 pm

#215465

I have a report which shows the working days of employees on month basis, from monday to friday.
Users want to see also saturday and sunday in the report but these days are not existing in my table "working_day". Is there a way to calculate saturday and sunday when I have for instance the following working days: 02.11.2009, 03.11.2009, 04.11.2009, 05.11.2009, 06.11.2009) and want to have this result: 02.11.2009, 03.11.2009, 04.11.2009, 05.11.2009, 06.11.2009, 07.11.2009, 08.11.2009?
Thanks
Mamadu

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the trick is to use a combination of dateadd and datediff, here's sme examples of some firsts and lasts; to apply the logic to a specific daye, like "what was the start of business (monday) for Nover 3rd, substitute your date for the getdate() in the examples.

--find the first business day (Monday) of this month

select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)

--find the last day of the prior month

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

--find the third friday of this month:

--14 two weeks plus the M-F offset of 4

select DATEADD(dd,18,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0))

select DATEADD(wk,2,DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)) )

--last business day(Friday) of the prior month...

select dateadd(ms,-3,dateadd(dd,-3,DATEADD(wk,

DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)))

--Monday of the Current Week

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

--Friday of the Current Week

select dateadd(dd,4,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0))

--First Day of this Month

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Or this:

declare @ThisDate datetime;

set @ThisDate = '2010-01-05'

select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month

select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1) -- End of this month

You can find some additional date related code here Some Common Date Routines

mtraore08 Right there with Babe Points: 771 More actions · Answer 3

Thanks a lot for your answer, I'll try it tomorow morning

mtraore08 Right there with Babe Points: 771 More actions · Answer 4

Many thanks for the prompt answer. I'll check the function tomorow in the morning.