Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Unanswered: difference between two datetime fields

    Hi ,
    I have a problem.I am running SQL server 7.0.I have 2 (datetime) fields in my Requests table called Inputtime and Finishedtime.I have a 3rd field called Timedifference.
    I would like to know how I could calculate the difference between these
    2 columns and have the value put into the third column.
    The input time and finished time fields are generated automatically using ASP and stored in my records database.
    Would I be able to use a trigger here?
    Any thoughts?
    Thanks

  2. #2
    Join Date
    May 2002
    Posts
    299

    re:difference between two datetime fields

    Create a calculated column for this...

    e.g.
    create table Table1 (dt1 datetime,dt2 datetime,dt3 as datediff(mi,dt1,dt2))
    go

    insert into Table1 values ('6/15/2002 1:10:00 AM',getdate())
    insert into Table1 values ('6/15/2002 7:10:00 AM',getdate())
    insert into Table1 values ('6/15/2002 8:10:00 AM',getdate())
    insert into Table1 values ('6/17/2002 1:10:00 AM',getdate())
    insert into Table1 values ('6/17/2002 7:10:00 AM',getdate())
    insert into Table1 values ('6/17/2002 8:10:00 AM',getdate())
    go
    select * from table1
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You can use a calculated column.

    Example:
    Code:
    set nocount on
    go
    create table test
      (
      col1    int identity(1,1),
      dtStart datetime,
      dtEnd   datetime,
      dtDiff  AS (DATEDIFF(dd,dtEnd,dtStart))
      )
    go
    insert test (dtStart,dtEnd) VALUES (GETDATE(),NULL)
    insert test (dtStart,dtEnd) VALUES (DATEADD(dd,-3,GETDATE()),NULL)
    insert test (dtStart,dtEnd) VALUES (DATEADD(dd,-3,GETDATE()),NULL)
    go
    select * from test
    go
    update test set dtEnd = GETDATE() WHERE col1=2
    go
    select * from test
    go
    OUTPUT
    Code:
    col1 dtStart                 dtEnd                   dtDiff
    ---- ----------------------- ---------------------   --------
    1    2002-07-09 10:16:07.733 NULL                    NULL
    2    2002-07-06 10:16:07.733 NULL                    NULL
    3    2002-07-06 10:16:07.750 NULL                    NULL
    
    col1 dtStart                 dtEnd                   dtDiff
    ---- ----------------------- ----------------------- -----------
    1    2002-07-09 10:16:07.733 NULL                    NULL
    2    2002-07-06 10:16:07.733 2002-07-09 10:16:07.780 3
    3    2002-07-06 10:16:07.750 NULL                    NULL
    You can use any measure on the DATEDIFF, seconds, minutes, etc...
    MCDBA

Posting Permissions

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