How to compare two dates to find time difference in SQL Server 2005, date manipulation

How to compare two dates to find time difference in SQL Server 2005, date manipulation

Asked on December 25, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    Use the function named, Datediff().

    -- Syntax
    -- DATEDIFF ( datepart , startdate , enddate )
    -- Example usage
    SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff
    SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
    SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff
    SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
    SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff
    ...
    
    Answered on December 25, 2018.
    Add Comment

    To get the difference in minutes, seconds, days and so on one have to use the DATEDIFF function.

    SELECT DATEDIFF(MINUTE,job_start,job_end)
    

    The code above MINUTE is the function that can gives the difference in minutes, we also use DAY, HOUR, SECOND, YEARS.

    To get desire or fancy we can show this differently for example 75 minutes , it can be displayed  01:15:00:0.

    For both SQL Server 2005 and 2008 this code will support ,

    -- SQL Server 2005
    SELECT CONVERT(VARCHAR(10),DATEADD(MINUTE,DATEDIFF(MINUTE,job_start,job_end),'2011-01-01 00:00:00.000'),114)
     
    -- SQL Server 2008
    SELECT CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,job_start,job_end),'2011-01-01 00:00:00.000') AS TIME)
    
    Answered on December 25, 2018.
    Add Comment

    Have a look at this one line code, this will give TIME and the format will be interval duration.

    select CAST(job_end - job_start) AS TIME(0)) from tableA
    
    Answered on December 25, 2018.
    Add Comment

    I have two columns:

    job_start                         job_end
    2011-11-02 12:20:37.247           2011-11-02 13:35:14.613

    How would it be possible using T-SQL to find the raw amount of time that has passed between when the job started and when the job ended?

    I tried this:

    select    (job_end - job_start) from tableA

    but ended up with this:

    1900-01-01 01:14:37.367
    Answered on January 13, 2019.
    Add Comment

    Take a look at the DateDiff() function.

    -- Syntax
    -- DATEDIFF ( datepart , startdate , enddate )
    
    -- Example usage
    SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff
    SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
    SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff
    SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
    SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff
    ...
    Answered on January 13, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.