Results 1 to 10 of 10

Thread: Compute datetime field addition

  1. #1
    Join Date
    May 2010
    Posts
    9

    Compute datetime field addition

    Hi,

    I have 2 columns - ScanDateTime and DataDateTime and both are DATETIME fields having values as given below..

    ScanDateTime DataDateTime
    2009-10-21 08:51:22 2009-10-21 09:53:16
    2009-10-21 10:19:27 2009-10-21 09:53:16
    2009-10-21 10:19:27 2009-10-21 10:44:50
    2009-10-21 10:44:50 2009-10-21 10:59:33
    2009-10-21 10:59:33 2009-10-21 09:18:53

    Now I want to add all the value in ScanDateTime field and in DataDateTime field such as..

    Code:
    Value = (Sum of timestamps for ScanDateTime + Sum of timestamps for DataDateTime)
    And Divide the data returned from Value by a Number ( say 1000).

    How can this be acheived ??

    Thanks.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    sunny297, what, exactly, do you want to accomplish?

    Also, Date functions / manipulation is very database dependent. An answer with DB2 syntax won't work in SQL Server, for example. Can you mention what Database you are using?

  3. #3
    Join Date
    May 2010
    Posts
    9
    Hi,

    The platform I am working on is SQL Server 2000.

    To rephrase my requirement again..

    Value1 = Sum of all timestamp for ScanDateTime

    Value2 = Sum of all timestamps for DataDateTime

    Value3 = (Value1 + Value2)/1000

    The datatype for Value1, Value2 and Value3 should be (hh24:mi:ss) format.

    The datatype for ScanDateTime and DataDateTime columns is (yyyy-mm-dd hh24:mi:ss) format.

    Looking forward for a prompt response.

    Thanks.

  4. #4
    Join Date
    May 2010
    Posts
    9
    Quote Originally Posted by sunny297 View Post

    The datatype for Value1, Value2 and Value3 should be (hh24:mi:ss) format.
    Sorry, it should be like - The datatype for Value3 should be (hh24:mi:ss) forma

  5. #5
    Join Date
    Apr 2009
    Posts
    86
    Not quite there yet, sunny297. Of itself, summing Timestamps does not make any sense.

    Are you trying to determine the total number of hours, minutes and/or seconds that ScanData took? (but then you would need a Start time and an End time)

    What I need is what your are ultimately trying to accomplish by 'adding' timestamps.

  6. #6
    Join Date
    May 2010
    Posts
    9
    Hi,

    Yes - I want something like what you mentioned, find the total hh:mm:ss for ScanDateTime abd DataDateTime respectively.

    It is one of the requirement in my project ( know it sounds bit off sense), but my client wants this columns (AverageTime or Value3) to be determined by the way ive just told you above. The client will use this field to get some other data ( which is out of my scope).

    Hope it helps.

    Thanks.

  7. #7
    Join Date
    Apr 2009
    Posts
    86
    I am still not sure if I understand as you need a start time and and end time to get an elapsed time. I am going to assume that Scan Date is when a Scan started and Data Date is when the Scan finished. If that is the case, what you want to do is get the difference between the two, sum up this difference and then divide by your number. The final result will be in seconds but you should be able to get hours, minutes and seconds out of that.

    This will return the number of Seconds between the start time of ScanDatetime and DateDatetime:

    DATEDIFF(S,ScanDatetime, DataDatetime)

    From there you should be able to add, subtract, average or do anything else you like with it.

    If this is not what you want, and ScanDatetime and DateDatetime are not the starting and stopping times, you still need something to determine elapsed time.

  8. #8
    Join Date
    May 2010
    Posts
    9
    Thanks for your answers so far !!

    I already have a field to calculated ELapsed time. The field about which I have issues is the Averagetime (or Value3).

    It needs to be the sum os ScanTime and DataTime divided by a number figure..

    Thanks.

  9. #9
    Join Date
    May 2010
    Posts
    9
    I used the below block to get the AverageTime field by adding the 2 date columns by first converting them to seconds and then dividing it with a number field.

    Code:
    begin
    
    declare @date1 nvarchar(100), @date2 nvarchar(100), @FinalDate nvarchar(100), @count int
    
    create table #temp6 (tks int, date1 nvarchar(100), date2 nvarchar(100), FinalDate nvarchar(100))
    
    select @count = count(eventcode) from trackedplusimport_temp where deviceid=13768
    SELECT @date1 = Convert(nvarchar(10),sum(DatePart(hh,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,ScanDateTime))) FROM trackedplusimport_temp WHERE deviceid=13768
    SELECT @date2 = Convert(nvarchar(10),sum(DatePart(hh,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,DataDateTime))) FROM trackedplusimport_temp WHERE deviceid=13768
    
    SELECT @FinalDate =
    convert(nvarchar(10),
    (
     (
    	(DATEPART(hour, convert(nvarchar,@date1,108)) * 60 * 60) +
    	(DATEPART(minute,convert(nvarchar,@date1,108)) * 60) +
    	 DATEPART(second, convert(nvarchar,@date1,108))
     )
     +
     (
    	(DATEPART(hour, convert(nvarchar,@date2,108)) * 60 * 60) +
    	(DATEPART(minute, convert(nvarchar,@date2,108)) * 60) +
    	 DATEPART(second, convert(nvarchar,@date2,108))
     )
    )
    )
    --select @c = @b/@count
    
    insert into #temp6 values(@count, @date1, @date2, @FinalDate)
    
    end
    
    --drop table #temp6
    --select * from #temp6
    When Executing the above, Im getting the below error..


    Arithmetic overflow error converting expression to data type datetime


    How can I overcome this error?

    Thanks in advance

  10. #10
    Join Date
    May 2010
    Posts
    9
    Just some more info..

    When Date1 and Date2 are upto : '23:59:59', the query is working fine. But when it becomes '24:00:00' - I am getting Overflow error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •