Computing Dates

I'm soliciting any suggestions from the readers of the group on how
best to tackle this task. I need to determine if trouble tickets
opened get closed within 4 hours. Yes, I already know about
datediff(hh, date1, date2, but it's not that simple. The thing I'm
struggle with is that I need to take inaccount business hours (8:00am
to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
10/28/05 10:00am. Any suggestions are much appreciated.Try,
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
else
'more than 4 hours'
AMB
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>|||Correction:
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 20) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Try,
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
> else
> 'more than 4 hours'
>
> AMB
> "BobD" wrote:
>|||Correction: (sorry guys)
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 16) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Correction:
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 20) <= 4)
> print 'may be in 4 hours'
> else
> 'more than 4 hours'
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Bob,
Try this:
SELECT
CASE WHEN cast(convert(varchar(12),date1,114) as datetime) < '08:00'
THEN dateadd(day,datediff(day,0,date1),'12:00
')
WHEN cast(convert(varchar(12),date1,114) as datetime) < '12:00'
THEN dateadd(hour,4,date1)
ELSE dateadd(hour,20,date1) END
FROM yourTable
Steve Kass
Drew University
BobD wrote:

>I'm soliciting any suggestions from the readers of the group on how
>best to tackle this task. I need to determine if trouble tickets
>opened get closed within 4 hours. Yes, I already know about
>datediff(hh, date1, date2, but it's not that simple. The thing I'm
>struggle with is that I need to take inaccount business hours (8:00am
>to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
>10/28/05 10:00am. Any suggestions are much appreciated.
>
>|||Bod
try this
SELECT CASE WHEN DATE1 = DATE2 THEN DATEDIFF(HOUR, DATE1,DATE2) ELSE
DATEDIFF(HOUR, DATE1,(DATEADD(HOUR,-16,DATE2)))
--
Regards
R.D
--Knowledge gets doubled when shared
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>