Solved

# Given a date, halve it... - bit of a challenge maybe

Posted on 2006-06-05
249 Views
Hi

Given a date of say 15/5/2006 13:00  and then given a time span of say 1 day, how do i then halve that date?

**There is a few catches**

1) Must take into consideration weekends
2) Must take into consideration working hours

As an example, today is the 5th June, if i have a time span of 1 day.

Add one day to 5th June 13:30 = 6th June 13:30
Halve one day (which is based on a 9-5 day, so 4 hours) = 6th June 09:30

Simple :o)

So, if was 6th June at 10:00 and you halved a day and subtracted it, taking into consideration working days it would work out to be the day before because it would be before 9am. Same applies to weekends.

How do i do this with SQL?????

Many Thanks
0
Question by:Type25

LVL 50

Accepted Solution

do you mean...

Select startdate
,case Datepart(dw,X)
When 6 then Dateadd(d,2,x)
else x
end as x

from (
Select Case when Datepart(hh,x) >= 17
dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
else x
end as x
,startdate
from (
select dateadd(hh,4,StartDate) as X,startdate
from (select '20060605 10:30:00' as startdate
union select '20060605 14:30:00'
union select '20060602 14:30:00'
) as qq
) as Y
) as z
0

LVL 27

Assisted Solution

You'll need a function to return the new date.

In pseudo code the calculation steps are these:

PeriodInHours = TimeSpan * Conversion Factor
HoursToAdd = PeriodInHours / 2
NewDate = StartDate + Days
NewDate = NewDate + Hours
-- Now take care of the weekends
EndDate = NewDate
Do while EndDate > StartDate
if WeekNo(EndDate) - WeekNo(StartDate) > 0
NewDate = NewDate + 2
end if
EndDate = EndDate - 7
Loop

I may get a chance later today to set up in TransactSQL if you need it, what are the possibilities for the input time span?

Hope this helps
0

LVL 9

Author Comment

Ughhh....

i'm trying to now use some params with your method, however, running into problems... ie:

DECLARE @StartDate       datetime
DECLARE @TimeSpan      int
SET @StartDate = '5 June 2006 12:00'
SET @TimeSpan = 240 -- minutes (4 hours)

Select @startdate
,case Datepart(dw,X)
When 6 then Dateadd(d,2,x)
else x
end as x

from (
Select Case when Datepart(hh,x) >= 17
dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
else x
end as x
,@startdate
from (
select dateadd(n,@TimeSpan,@StartDate) as X) as Y
) as z

Help! :)

Hi Mike, cheers, i may give that a go myself, the interval will be in minutes, ranges from 15 mins to 5 days (7200 mins)

Thanks
0

LVL 4

Assisted Solution

Declare @date dateTime
Declare @tSpan int
set @tSpan = 240
set @date = '06/05/2006 10:00:00'
set @date = dateadd(mi, @tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t < 9)
Begin
set @date = dateadd(d, -1, @date)
set @date = dateadd(hh, 8, @date)
End
Else if (@t > 17)
Begin
set @date = dateadd(d, 1, @date)
set @date = dateadd(hh, -8, @date)
End

--Fix Date
set datefirst 6
set @t = DatePart(dw, @date)
print @t
set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date

Assuming that when subtracting timespan, u end up in a weekend, u choose friday (the last working day), but when adding timespan, u end up in a weekend, u choose the first week day.

Hope it is what u need.
But if u want to find other ways, keep in mind that you will have to process the time first and then the date, to avoid weekends.

NM
0

LVL 50

Expert Comment

DECLARE @StartDate      datetime
DECLARE @TimeSpan     int
SET @StartDate = '5 June 2006 12:00'
SET @TimeSpan = 240 -- minutes (4 hours)

Select @startdate
,case Datepart(dw,X)
When 6 then Dateadd(d,2,x)
else x
end as x

from (
Select Case when Datepart(hh,x) >= 17
dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
else x
end as x
--     ,@startdate     --<   this originally was just how i passed my test dates up to the top select...
from (
select dateadd(n,@TimeSpan,@StartDate) as X) as Y
) as z
0

LVL 50

Expert Comment

ps my code assumes that the start time will only be Monday to friday...
and is also based on the assumption that the timespan won't go over midnight ...

0

LVL 9

Author Comment

Nazermohideeen

Bit confused by that logic.......  i've put in a few different time spans and it seems to land on weekends

The start time could potentially occour on a weekend and the timespan could range from 15 mins to 7200 mins....
Any chance of showing me how that might change things? :)

Appreciate the help guys.

0

LVL 9

Author Comment

What might be easier is if i demonstrate the idea behind what i'm trying to accomplish.

There are predefined dates for service standards which range from 15 mins to 5 days.

The user is asked to enter dates into the system, these days might be

Contacted customer
Visited customer
Report submitted

Once the user has entered a date for Contacted customer, there is a service standard for when they must enter a visited customer date, this for example is 4 days after.

Once the users the date, we then store the 'NextAlertDate' for the users information. This alert date is half of the service standard and must ONLY be visible to the user between mon-fri 9-5.

So for example, a user puts in a contacted customer date and the service standard is 4 days, the next alert time will be in 2 days time (but must be between 9-5 and mon-fri), so if the user filled out the date on the fri, the next alert time would be set to Tues (skipping the weekend)

0

LVL 4

Expert Comment

Hi,

what is the date format you are giving. My code assumes American Date Format (mm/dd/yyyy)

If this works, will explain the logic

NM
0

LVL 9

Author Comment

Date format here is UK: dd/MM/yyyy

0

LVL 9

Author Comment

NM, for example:

Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days
set @date = '05 June 2006 10:00:00'
set @date = dateadd(mi, @tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
set @date = dateadd(d, -1, @date)
set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
set @date = dateadd(d, 1, @date)
set @date = dateadd(hh, -8, @date)
End

--Fix Date
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date

is incorrect, adding 7200 (5 days / 2) = 2.5 days to 5 June, still comes out as the 5 June ??

0

LVL 4

Expert Comment

If then just add in the first line

set dateformat dmy

It should work

NM
0

LVL 9

Author Comment

Still same problem, have a look at my example above....

Thanks
0

LVL 4

Expert Comment

ok I understood, I assume the tspan is 24 hours a day, But u need it as 8 hours a day

Hold on a minute, I will check again
NM
0

LVL 9

Author Comment

Great, thanks!
0

LVL 4

Expert Comment

Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days

set @date = '05 June 2006 10:00:00'

Declare @temp_tSpan int
set @temp_tSpan  = @tSpan + Round(@tSpan / 480, 0) * 960

set @date = dateadd(mi, @temp_tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
set @date = dateadd(d, -1, @date)
set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
set @date = dateadd(d, 1, @date)
set @date = dateadd(hh, -8, @date)
End

--Fix Date
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date

This MUST WORK

NM
0

LVL 9

Author Comment

I'll have a play and come back to you ;o)

0

LVL 9

Author Comment

Close.... really close

However, if you put in a start date of 8th June and then add 2.5 days you end up on Sunday

Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days

set @date = '08 June 2006 10:00:00'

Declare @temp_tSpan int
set @temp_tSpan  = @tSpan + Round(@tSpan / 480, 0) * 960

set @date = dateadd(mi, @temp_tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
set @date = dateadd(d, -1, @date)
set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
set @date = dateadd(d, 1, @date)
set @date = dateadd(hh, -8, @date)
End

--Fix Date
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date
0

LVL 9

Author Comment

if datename(dw,@date) = 'Saturday' set @date = dateadd(d,2,@date)
if datename(dw,@date) = 'Sunday' set @date = dateadd(d,1,@date)

seems to do the trick though...
0

LVL 50

Expert Comment

does this work...

DECLARE @StartDate      datetime
DECLARE @TimeSpan     int
SET @StartDate = '5 June 2006 12:00'
SET @TimeSpan = 1240 -- minutes (4 hours)

Select @startdate
,case Datepart(dw,X)
When 6 then Dateadd(d,2,x)
else x
end as x

from (
Select Case when Datepart(hh,x) >= 17
dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
When Datepart(hh,x) < 9
else x
end as x
--     ,@startdate
from (
select dateadd(d,2 * (@timespan / 480) ,dateadd(d,@timespan / 480,dateadd(n,@TimeSpan % 480,@StartDate))) as X) as Y
) as z

0

LVL 50

Expert Comment

ie
a work day is 480 hours....
between 9 and 5 ...

can you give some examples of what you'd expect to happend if the starttime was say

6pm on thurs
6pm on Friday

8am on Thursday...

for differing durations ...

hth
0

LVL 9

Author Comment

Nope, because if you stick in the 8th June and then 1200 minutes, it works it out to be weds and it should be monday (2.5 days + 8th June excluding weened)
0

LVL 9

Author Comment

>> can you give some examples of what you'd expect to happend if the starttime was say

sure....

Main thing to keep in mind is the service standards are halved in actual days but it should be worked out in working hours.

I'll work out some times....

0

LVL 9

Author Comment

6pm on thurs  - Duration of 2.5 days (3600 mins (actual mins)) - Tues @ 1PM
6pm on Friday - Duration of 2.5 days (3600 mins) - Weds @ 1PM

8am on Thursday... - Duration of 2 days (2800 mins) - Friday 5PM - Rolled back to the last working day

Does that make sense?
0

LVL 9

Author Comment

The rolled back bit is the most complicated i think.... if it ends up working out to be between > 5  and < 9 then it will equal 5pm the day before...
0

LVL 50

Expert Comment

SET @StartDate = '20060608 12:00' --'5 June 2006 12:00'
SET @TimeSpan = 1200 --1240 -- minutes (4 hours)

Select @startdate
,case Datepart(dw,X)
When 6 then Dateadd(d,2,x)
when 7 then dateadd(d,1,x)
else x
end as x

from (
Select Case when Datepart(hh,x) >= 17
dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
When Datepart(hh,x) < 9
else x
end as x
--     ,@startdate
from (
select dateadd(d,2 * (@timespan / 480 / 7) ,dateadd(d,@timespan / 480,dateadd(n,@TimeSpan % 480,@StartDate))) as X) as Y
) as z

0

LVL 9

Author Comment

I may not have made it too clear...

2880 = 2 days

If your start date is 8th June and you try and add 2880 minutes, it ends up being the following weds! (would normally land on the saturday at 12:00 but should be rolled back to 5pm on the friday.

Still appreciating the help.. !!
0

LVL 50

Expert Comment

sorry forgot the divide by 7 on the week adjustment../.
0

LVL 50

Expert Comment

>The rolled back bit is the most complicated i think.... if it ends up working out to be between > 5  and < 9 then it will equal 5pm the day before...

yes because its not clear ...

if start at 4pm day1 for duration 5 hours then ends day2 1pm...

if start at day1 a2am for duration 3hours you want it to end on day0 at 5pm?
0

LVL 9

Author Comment

if start at 4pm day1 for duration 5 hours then ends day2 1pm...  - correct, as long as the end calculation ends between 9-5 it's fine.

if start at day1 a2am for duration 3hours you want it to end on day0 at 5pm? - interesting one... but yes it would be rolled back to day0 @ 5pm.

I should have probably mentioned that at the moment the durations are never less than one working day 1440 minutes.
Anything less than that is worked out real time and working hours becomes redundent.

So in theory that second calculation will never be done.

0

LVL 27

Expert Comment

This almost gets you there, let me know if you want more work on it:

create function TargetDate(@StartDate smalldatetime, @Interval integer)
returns smalldatetime
as
begin
Declare @NewDate smalldatetime
set @NewDate = @StartDate
set @interval = @interval / 2
Set @NewDate = DateAdd(d, @interval/(24*60), @NewDate)
Set @NewDate = DateAdd(mi, @interval%2880, @NewDate)

if datepart(dw, @NewDate - datepart(dw,@StartDate)) > 4
Set @NewDate = dateadd(d, 2, @NewDate)
return @NewDate
end
0

LVL 4

Expert Comment

There is a bit confusion here

You say that 3600 mins = 2.5 days = 60 hours =  2 days 12 hours.
But you need 2 days 4 hours when u specify 3600

I could also see when u tried to test my example, you specify 2400 = 5 days, which is 8 hours a day. and now if you say 2400 / 2 = 2.5 days which is  days and 4 hours makes sense

First be clear with the requirement.

ofcourse yes, there was a bug with my code fragment, in last line

The following conditions are assumed,
One day = 8 hours, so 5 days = 2400 mins
The start time will fall in a working hours and in a working day. (if not, you will have to round the day to the closest working hour)

--------------------------------------
Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days

set @date = '08 June 2006 10:00:00'

Declare @temp_tSpan int
set @temp_tSpan  = @tSpan + Round(@tSpan / 480, 0) * 960

set @date = dateadd(mi, @temp_tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
set @date = dateadd(d, -1, @date)
set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
set @date = dateadd(d, 1, @date)
set @date = dateadd(hh, -8, @date)
End

--Fix Date
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, 3 - @t, @date)
Print @date

NM
0

LVL 9

Author Comment

Ok, sorry about the confusion, perhaps i should start again ;)

I need to add a specified number of minutes  (1 day = 1440) to a given date.

If when the minutes are added the new date is not between 9-5 then the date should be rolled back to the last working day at 5pm

So for example:

Start Date: 8th June 2006 12:00

Interval: 2 days (2880 minutes)
End result: 9th June 2006 5PM  (would have landed on a saturday so rolled back to last working day)

Interval: 1 day (1440 minutes)
End result: 9th June 2006 12:00

If the start date is 6th June 6PM and the interval is 1440 minutes then the end result would be 7th June 5PM

Is that any clearer?

0

LVL 9

Author Comment

OK, i've had a dabble myself, and i think it's as simple as this:

Declare @tSpan int
set @tSpan = 2880
Set @tSpan = @tSpan / 2

set @date = '05 June 2006 10:00:00'

set @date = dateadd(mi,@tSpan,@date)

if datePart(hh, @date) >= 17 AND datepart(hh,@date) <= 23 SET @date = convert(char(8),@date,112)+' 17:00:00.000'
if datePart(hh, @date) >= 1 AND datepart(hh,@date) < 9 SET @date = dateadd(d,-1,convert(char(8),@date,112)+' 17:00:00.000')

if datename(dw,@date) = 'Saturday' set @date = dateadd(d,-1,convert(char(8),@date,112)+' 17:00:00.000')
if datename(dw,@date) = 'Sunday' set @date = dateadd(d,-2,convert(char(8),@date,112)+' 17:00:00.000')

print @date

don't think it's flawed.
0

LVL 9

Author Comment

Thanks everyone for helping out, to make it fair i'll split the points between all of you.

Hope this is ok.

Thanks again.
0

## Join & Write a Comment Already a member? Login.

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

#### 746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!