[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2007-12-03
15
Medium Priority
?
299 Views
Last Modified: 2008-02-01
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
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
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

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

0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20399380
In that case, change "mi" to "s"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20399390
You'll also have to pad your callduration...  '00:' + callduration
0
 
LVL 17

Author Comment

by:MIKE
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

by:MIKE
ID: 20399404
Callduration is NVARCHAR (50)
0
 
LVL 17

Expert Comment

by:Chris Mangus
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

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

0
 
LVL 17

Author Comment

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

Expert Comment

by:Chris Mangus
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

by:MIKE
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

by:MIKE
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

by:
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

by:MIKE
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

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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

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

Join & Ask a Question