[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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

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
MIKE
Asked:
MIKE
  • 8
  • 7
1 Solution
 
Chris MangusDatabase AdministratorCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
No this should be SECONDS ...not minutes.....

0
 
Chris MangusDatabase AdministratorCommented:
In that case, change "mi" to "s"
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Chris MangusDatabase AdministratorCommented:
You'll also have to pad your callduration...  '00:' + callduration
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
Callduration is NVARCHAR (50)
0
 
Chris MangusDatabase AdministratorCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
Yes...the Duration is NVARCHAR (50)...and the DATE and TIME are.....DATETIME........

0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Shouldn't I conver to SECONDS...and then add ....and then....convert back to DATE/TIME...?
0
 
Chris MangusDatabase AdministratorCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
Glad you have a solution!  Nice code...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now