Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Unanswered: Date1 - Date2 in time format... Easily doable?

    Hello all,

    In a table, I have a beginning date and an end date, both in a datetime format. All I want to do, is calculate the difference between the two.

    That sounds simple enough. But I'm still writing on a forum about it...

    I need the results in a format like HH:MI:ss. Those two columns are the start and end time of an ETL process and I want to report on the time the job takes to run.

    So... Any ideas? I tried to use the DATEDIFF function using the second as the date part. Works great, I now have an integer representing the number of seconds... how can I convert 7745 seconds to HH:MI:ss?

    Is there an easy way or do I need to go through the mathematical divisions?

    Thanks

  2. #2
    Join Date
    Jul 2002
    Posts
    42
    OK. So I found this out, it returns a string formated :

    select
    convert(char(30), dateadd(ss, datediff(second, '2006-10-30 02:00:00', '2006-10-30 21:17:47'), "00:00:00"), 108)

    Returns the string: '19:17:47'

    Works great only within 24 hours. And what can you do with a string? Can't calculate averages, sums, etc...

    Any ideas?

    Thanks again.

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Sybase doesn't have an "interval" datatype, so the best thing is probably to keep the data in seconds (so that you can do averages, etc), and only convert it to a printable string when necessary (i.e. for display). The conversion from seconds to an H:M format could be done in the client app, or with an appropriate combination of divisions and modulo operations:
    Code:
    [495] DBA_SQL.histo.1> select str(datediff(ss, 'oct 31 2006 11:40', 'nov 1 2006 20:00') / 60 / 60, 3, 0) + ':' + str((datediff(ss, 'oct 31 2006 11:40', 'nov 1 2006 20:00') % (60 * 60)) / 60, 2, 0);
    
     ------
      32:20
    (I skipped the seconds, but you get the idea :-)

    Michael

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Some options I came up with
    Code:
    -- Generate some test data
    select * into #t1 
    from ( select
    'jobid'=1,'strtdt'='2006-10-30 02:00:00', 'enddt'='2006-10-30 21:17:47' union all select
    'jobid'=1,'strtdt'='2006-10-31 02:00:00', 'enddt'='2006-10-31 19:17:49' union all select
    'jobid'=2,'strtdt'='2006-10-30 01:00:00', 'enddt'='2006-10-31 02:20:09' union all select
    'jobid'=2,'strtdt'='2006-10-30 01:00:00', 'enddt'='2006-10-31 02:50:05' union all select
    'jobid'=2,'strtdt'='2006-10-31 01:00:00', 'enddt'='2006-11-01 02:40:07' union all select
    'jobid'=3,'strtdt'='2006-10-01 01:00:00', 'enddt'='2006-11-02 02:40:07'
    ) v1
    -- End Generate some test data
    
    select jobid
    ,'mintime' =str(min(runsec)/60/60,4,0)+':'+right(
               convert(char(8),dateadd(ss,min(runsec),'1900-01-01'),108),5)
    ,'avgtime1'=str(avg(runsec)/60/60,4,0)+':'+right(
                convert(char(8),dateadd(ss,avg(runsec),'1900-01-01'),108),5)
    ,'avgtime2'=str_replace(str(avg(runsec)/60/60,4,0)+':'
               +str((avg(runsec)-(avg(runsec)/60/60)*60*60)/60,2,0)+':'
               +str((avg(runsec)-(avg(runsec)/60/60)*60*60)
               -((avg(runsec)-(avg(runsec)/60/60)*60*60)/60)*60,2,0) ,': ',':0')
    ,'avgtime3'=str(datediff(dd,'1900-01-01',dateadd(ss,avg(runsec),'1900-01-01')),3,0)+' Day(s) '
               +convert(char(8),dateadd(ss,avg(runsec),'1900-01-01'),108) 
    from 
    (select jobid,strtdt,enddt,'runsec'=datediff(ss,strtdt,enddt)
     from #t1
    ) view1
    group by jobid
    
    drop table #t1
    
    jobid       mintime    avgtime1   avgtime2   avgtime3            
    ----------- -------    --------   --------   --------            
              1   17:17:49   18:17:48   18:17:48   0 Day(s) 18:17:48 
              2   25:20:09   25:36:47   25:36:47   1 Day(s) 01:36:47 
              3  769:40:07  769:40:07  769:40:07  32 Day(s) 01:40:07

Posting Permissions

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