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

    How to remove duplicate records from datetime column

    Hi,
    I have a table which is have unique id and datetime columns, the datetime column is having records as below
    2010-08-17 14:50:14.323
    2010-08-17 14:50:14.730
    2010-08-17 14:50:14.807
    2010-08-17 14:50:15.010
    2010-08-17 14:50:15.357
    ...
    2010-08-17 14:51:09.810
    2010-08-17 14:51:10.047
    2010-08-17 14:51:10.250
    2010-08-17 14:51:10.500
    2010-08-17 14:51:10.890
    2010-08-17 14:51:10.953
    I want only one records per day all remaining records need to be delete.
    Plese givea suggestion.
    Regards,
    Sankar.A
  • #744193
    Hi Sankar,

    Could you please explain briefly. How and when the date is inserting. Do you want only to delete the record from same day

    -----------------------------------------------------------------------------
    Regards,
    Gopi A.
    +91 9894315571
    Skype:gopi.net
    http://asaigopi-dotnet.blogspot.in/

  • #744195
    Hi,

    As per my understand the post you don't want to insert duplicate rows on the same day right..?

    In that case better to use Columns datatype as "Date"then it will store only date values and put that date column in PK relation then it accept only one insert.

    Hope you understood....

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #744198
    Hi,

    If you want to keep single record for a date and remove all others from an existing table then you can use below query,

    CREATE TABLE #Temp
    (
    [unique id] INT IDENTITY(1,1),
    DateCol DATETIME
    )

    INSERT INTO #Temp
    SELECT '2010-08-17 14:50:14.323' UNION ALL
    SELECT '2010-08-17 14:50:14.730' UNION ALL
    SELECT '2010-08-17 14:50:14.807' UNION ALL
    SELECT '2010-08-17 14:50:15.010' UNION ALL
    SELECT '2010-08-17 14:50:15.357' UNION ALL
    SELECT '2010-08-17 14:51:09.810' UNION ALL
    SELECT '2010-08-17 14:51:10.047' UNION ALL
    SELECT '2010-08-17 14:51:10.250' UNION ALL
    SELECT '2010-08-17 14:51:10.500' UNION ALL
    SELECT '2010-08-17 14:51:10.890' UNION ALL
    SELECT '2010-08-17 14:51:10.953'
    --(11 row(s) affected)

    SELECT A.* FROM #Temp A
    INNER JOIN
    (SELECT CONVERT(DATETIME,CONVERT(VARCHAR,DateCol,101)) AS DateCol,MIN([unique id]) AS MIN_UNIQ_ID FROM #Temp
    GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,DateCol,101)) ) B
    ON A.[unique id] <> B.MIN_UNIQ_ID
    --(10 row(s) affected)

    DELETE A FROM #Temp A
    INNER JOIN
    (SELECT CONVERT(DATETIME,CONVERT(VARCHAR,DateCol,101)) AS DateCol,MIN([unique id]) AS MIN_UNIQ_ID FROM #Temp
    GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,DateCol,101)) ) B
    ON A.[unique id] <> B.MIN_UNIQ_ID
    --(10 row(s) affected)

    SELECT * FROM #Temp
    --(1 row(s) affected)

    Cheers,
    Vignesh Kannan

  • #744286
    Hi Sankar,

    Try this one also, CTE

    Eg :

    --> find duplicates

    with TempTable as (
    select row_number() over (partition by empid,datecolumn order by empid) as rownumber,* from Table)
    select * from TempTable where rownumber > 1

    -------------------------------------------------------------------------------

    --> delete duplicates

    with TempTable as(
    select row_number() over(partition by empid,datecolumn order by empid)as rownumber,* from Table)
    delete from TempTable where rownumber > 1


  • Sign In to post your comments