How to add minutes to a date within Working Hours

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to add minutes to a date within Working Hours

Author

Topic

P_Presland
Starting Member

2 Posts

Posted - 2014-09-03 : 05:58:59

I need to be able to add minutes to a datetime value, which only cover working hours.

I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday

Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)

Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05

I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly

Any assistance you can provide would be very gratefully received

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-09-03 : 09:02:28

You can use DateAdd to add the minutes, for example:

declare @dateadd datetime = '01/09/2014 10:00'
declare @dateadd2 datetime = '01/09/2014 17:00'

select @dateadd [startdate], dateadd(mi, 30, @dateadd) [expected]
, @dateadd2 [startdate2], dateadd(mi, 30, @dateadd2) [expected2]

If you need to check working hours you will also need to reference business hours for start and end times prior to adding the minutes.

We are the creators of our own reality!

P_Presland
Starting Member

2 Posts

Posted - 2014-09-03 : 09:18:29

Thanks for the response sz1, however that does not answer my question.

I need to be able to add minutes only within working hours on business days

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources