CODEWITHSUNDEEP
sqlsql-serverdatabasedatedatetime
Yasin Bilir
Yasin Bilir

Reputation: 304

How to get datetime duplicate rows in SQL Server?

Im trying to find duplicate DATETIME rows in a table,

My column has datetime values such as 2015-01-11 11:24:10.000.

I must get the duplicates in 2015-01-11 11:24 type. Rest of it, not important. I can get the right value when I use SELECT with 'convert(nvarchar(16),column,121)', but when I put this in my code, I have to use 'group by' statement, so

My code is:

  SELECT ID,
         RECEIPT_BARCODE,
         convert(nvarchar(16),TRANS_DATE,121),
         PTYPE
    FROM TRANSACTION_HEADER
   WHERE TRANS_DATE BETWEEN '11.01.2015' AND '12.01.2015' 
GROUP BY ID,RECEIPT_BARCODE,convert(nvarchar(16),TRANS_DATE,121),PTYPE
  HAVING COUNT(convert(nvarchar(16),TRANS_DATE,121)) > 1

Since SQL forces me to use 'convert(nvarchar(16),TRANS_DATE,121)' in GROUP BY statement, I can't get the duplicate values.

Any idea for this?

Thanks in advance.

Upvotes: 1

Views: 2632

Answers (2)

dev
dev

Reputation: 981

SELECT   ID,RECEIPT_BARCODE,convert(nvarchar(16),TRANS_DATE,121), PTYPE ,COUNT(*)
FROM    TRANSACTION_HEADER
WHERE   TRANS_DATE BETWEEN '11.01.2015' AND '12.01.2015' 
GROUP   ID,RECEIPT_BARCODE,convert(nvarchar(16),TRANS_DATE,121), PTYPE
HAVING   COUNT(*)>1;

I think you can use count(*) directly here.try the above one.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you want the actual rows that are duplicated, then use window functions instead:

SELECT  th.*, convert(nvarchar(16),TRANS_DATE,121)
FROM (SELECT th.*, COUNT(*) OVER (PARTITION BY convert(nvarchar(16),TRANS_DATE,121)) as cnt
      FROM TRANSACTION_HEADER th
      WHERE TRANS_DATE BETWEEN '11.01.2015' AND '12.01.2015' 
     ) th
WHERE cnt > 1;

Upvotes: 1

Related Questions