sql select duplicates by ignore seconds in date format

Asked Jun 12 '14 at 08:07

Active Jun 12 '14 at 08:07

Viewed 70 times

in my table Date store with seconds like 26-06-2014 09:24:25

i want to select duplicate values order by [Date] it takes seconds portion also but i need to select duplicates order by [Date] without seconds part

my current code is

WITH x AS (SELECT *, rn = row_number() OVER (PARTITION BY Date, Product,ExpiryDate ORDER BY Date)FROM Eod) Select * FROM x WHERE rn > 1

if i have two Dates like 26-06-2014 09:24:25 & 26-06-2014 09:24:30 then i need any one of two records but it shows nothing by taking seconds prt also.I want to ignore seconds part to show a record

asked Jun 12 '14 at 08:07

Raj

Use `cast([Date] as Date)` – Bharadwaj Jun 12 '14 at 08:09
i just want to exclude seconds part from time your code excludes total time part – Raj Jun 12 '14 at 08:19
you try group by for example select Date, Product,ExpiryDate from Eod Group by Date, Product,ExpiryDate ORDER BY Date – Hiral Nayak Jun 12 '14 at 08:19
it works with CONVERT(VARCHAR(19),Date) – Raj Jun 12 '14 at 08:29

sql select duplicates by ignore seconds in date format

0 Answers0