x
Solved

# Help Adding Date and Time and then calculating  ENDTIME by using Duration.......?

Posted on 2007-12-03
Medium Priority
308 Views
Experts:

My data table fields look like this:

date                            time                            callduration
2007-02-09 00:00:00.000      1900-01-01 15:21:12.000      00:06
2007-02-09 00:00:00.000      1900-01-01 15:22:07.000      00:00
2007-02-09 00:00:00.000      1900-01-01 15:22:48.000      01:06
2007-02-09 00:00:00.000      1900-01-01 15:26:53.000      01:23

I need to first of combine DATE and TIME into one field.... I know how to do this I simply ADD them together. It works fine.

But what I know need to calculate is and ENDDATETIME..for our calls.

So I need SQL for this:

select [DATE]+[TIME] + DURATION
from mytable

What would be the correct syntax to do this...?

Thanks
MikeV
0
Question by:MIKE
• 8
• 7

LVL 17

Expert Comment

ID: 20399350
Assuming 00:06 means 0 hours, 6 minutes, try:

Select DateAdd(mi, DatePart(mi, Convert(DateTime, CallDuration, 108)), Convert(Datetime, [Date], 101) + convert(Datetime, [Time], 101) )

0

LVL 17

Author Comment

ID: 20399364
No this should be SECONDS ...not minutes.....

0

LVL 17

Expert Comment

ID: 20399380
In that case, change "mi" to "s"
0

LVL 17

Expert Comment

ID: 20399390
0

LVL 17

Author Comment

ID: 20399400
Get this error...?

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

(0 row(s) affected)

Using this SQL:

Select DateAdd(s, DatePart(s, Convert(DateTime, CallDuration, 108)), Convert(Datetime, [Date], 101)
+ convert(Datetime, [Time], 101) )
from dbo.View_smdrdata_Phone_System
where calltype = 'IN'
and [date] between '2007-11-22' and '2007-11-25'
and importfilename not like '%intercom%'

Thanks
MikeV
0

LVL 17

Author Comment

ID: 20399404
Callduration is NVARCHAR (50)
0

LVL 17

Expert Comment

ID: 20399446
The SELECT portion of your query runs on my test server, with callduration as an NVARCHAR and both [Date] and [Time] as datetime.  Are these datetime in your database?

Be aware that in your BETWEEN clause, you will only get dates after 2007-11-22 00:00:00 and before 2007-11-25 00:00:00, so, you're not really going to pick up all the dates from 11/25/2007.
0

LVL 17

Author Comment

ID: 20399507
Yes...the Duration is NVARCHAR (50)...and the DATE and TIME are.....DATETIME........

0

LVL 17

Author Comment

ID: 20399524
Shouldn't I conver to SECONDS...and then add ....and then....convert back to DATE/TIME...?
0

LVL 17

Expert Comment

ID: 20399525
When you run the SELECT without the WHERE do you still get the error?

Select DateAdd(s, DatePart(s, Convert(DateTime, CallDuration, 108)), Convert(Datetime, [Date], 101)
+ convert(Datetime, [Time], 101) )
from dbo.View_smdrdata_Phone_System
0

LVL 17

Author Comment

ID: 20399538
YEP...same error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

(0 row(s) affected)

Shouldn't I convert to SECONDS the duration first...?

Call duration can look like this:   89:21

0

LVL 17

Author Comment

ID: 20399569
I think the issue is HOW the duration is stored.....the MINUTES will continue to accrue..and NOT change to hours...so this could be the case:      115:06    or    99:25   ETC...ETC..

How can I deal with converting this...correctly.....I think this is the issue...

MikeV
0

LVL 17

Accepted Solution

Chris Mangus earned 2000 total points
ID: 20399662
There is your problem.  I would think you need a UDF for that.  I don't know of a specific link for code for a UDF that will convert this for you but I would imagine that a quick Google search would find it.  Sorry I don't have an immediate answer for that.
0

LVL 17

Author Comment

ID: 20399723
I found the solution...in my history folder.....lol

Call it using:

SELECT dbo.fn_convert_to_ddhhmmss(CallDuration)

create function fn_convert_to_ddhhmmss(@input as varchar(50))
returns varchar(50)
as
begin
declare @totalMins int
declare @days smallint
declare @hours smallint
declare @minutes smallint
declare @seconds smallint
declare @returnValue varchar(50)
set @days = 0
set @hours = 0
set @minutes = 0
set @seconds = 0

set @seconds = case when charindex(':', @input) > 0 then substring(@input, charindex(':', @input) + 1, 2) else 0 end
set @totalMins = left(@input, case when charindex(':', @input) > 0 then charindex(':', @input) - 1 else len(@input) end)

if @totalMins >= 1440
begin
set @days = @totalMins / 1440
set @totalMins = @totalMins - (@days * 1440)
end

if @totalMins >= 60
begin
set @hours = @totalMins / 60
set @totalMins = @totalMins - (@hours * 60)
end

set @minutes = @totalMins

set @returnValue =
case
when @days = 0 then ''
else cast(@days as varchar(2)) + ':'
end +
case
when @hours = 0 then '00'
when @hours < 10 then '0' + cast(@hours as varchar(1))
else cast(@hours as varchar(2))
end + ':' +
case
when @minutes = 0 then '00'
when @minutes < 10 then '0' + cast(@minutes as varchar(1))
else cast(@minutes as varchar(2))
end + ':' +
case
when @seconds = 0 then '00'
when @seconds < 10 then '0' + cast(@seconds as varchar(1))
else cast(@seconds as varchar(2))
end
return @returnValue
end
0

LVL 17

Expert Comment

ID: 20399735
Glad you have a solution!  Nice code...
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.