Results 1 to 5 of 5

Thread: Full time diff between dates

  1. #1
    Join Date
    Feb 2006
    Posts
    6

    Full time diff between dates

    Hi all

    I am trying to get the day diff in days, hours, minutes between 2 dates

    I was able to do so in Oracle but haven't succeeded in SQL server


    Here is the code:

    select

    DateDiff(Day, PROCESS.date_created, PROCESS.date_modified)as days,

    datediff(hour,PROCESS.date_created, PROCESS.date_modified) as hours,

    datediff(minute,PROCESS.date_created, PROCESS.date_modified) as minues

    from

    PROCESS

    How ever they are ther are not calculated all together



    How can I combine them into one full time diff (days, hours, minutes) ?

  2. #2
    Join Date
    Aug 2007
    Posts
    1
    can i get sql software from u.

  3. #3
    Join Date
    Dec 2004
    Posts
    502
    Here's one way to do it:

    declare @date1 datetime, @date2 datetime

    set @date1 = '2007-01-01 00:00:00'
    set @date2 = '2007-01-02 01:15:00'


    SELECT DATEDIFF(day, 0, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))
    SELECT DATEPART(hour, CONVERT(varchar, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0), 108))
    SELECT DATEPART(minute, CONVERT(varchar, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0), 108))

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    Actually, that was a little bit of overkill. I simplified it a bit:

    declare @date1 datetime, @date2 datetime

    set @date1 = '2007-01-01 00:00:00'
    set @date2 = '2007-01-02 01:15:00'


    SELECT DATEDIFF(day, 0, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))
    SELECT DATEPART(hour, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))
    SELECT DATEPART(minute, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))

  5. #5
    Join Date
    Feb 2006
    Posts
    6

    day diff

    Worked like a charm!!

    nosepicker you are a SQL artist !

Posting Permissions

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