You must Sign In to post a response.
  • Category: SQL Server

    How to Compare two columns in database ?


    Are you looking for a way to Compare two columns in database ? then read this thread to know how to compare it



    Hi Team,

    I want to compare two date rows within a column

    I want to show the result like this:



    Time Difference
    ============================================
    2014-08-05 13:43:42.000 0
    2014-08-05 13:44:49.000 1
    2014-08-05 13:46:47.000 2
  • #748919
    Hi Use the below script

    CREATE TABLE runtotaltestdata
    (
    id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    value DATETIME NOT NULL
    );

    INSERT INTO runtotaltestdata
    VALUES ('2014-08-05 13:43:42.000')

    INSERT INTO runtotaltestdata
    VALUES ('2014-08-05 13:44:49.000')

    INSERT INTO runtotaltestdata
    VALUES ('2014-08-05 13:46:47.000')

    SELECT *
    FROM runtotaltestdata

    SELECT a.id,
    a.value,
    (SELECT DATEDIFF(mi, b.value, a.value)
    FROM runtotaltestdata b
    WHERE ( b.id + 1 ) = a.id)
    FROM runtotaltestdata a
    ORDER BY a.id;

  • #748940
    you can use SQL server 'DateDiff' function which
    This function Returns the number of date and time boundaries crossed between two specified dates.
    check out some sample below

    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

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #748950
    Both answers are wrong,

    i'm asking that

    In SQL Server best way to calculate datediff between current row and next row?

  • #749001
    Hai Christopher F,
    If you want to compare two dates of the same table, you need to use the Self Join for the same table and then you can compare them based on the two instances of the table as below:

    SELECT DATEDIFF(MINUTE, A.DOJ,B.DOJ) Difference
    FROM MyTable B
    join MyTable A
    on A.ID = B.ID - 1

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


  • Sign In to post your comments