[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

Compare the Date and Time of the same column in the same table

I need to find multiple occurrences of the exact date and time in the same "create date" column in the same table. We had an issue where a return authorization record was created on the same date and at exactly the same time, it had the same address but different names and serial numbers, I am trying to determine if there are any more that may have occurred and put some preventives measures in place to catch this.     Exact Date and Time
0
skull52
Asked:
skull52
  • 2
1 Solution
 
Patrick MatthewsCommented:
To find the duplicates...

SELECT DateCreated, COUNT(1) AS Cnt
FROM YourTable
GROUP BY DateCreated
HAVING COUNT(1) > 1

Open in new window

0
 
MohitPanditCommented:
To fetch:

SELECT
	*
FROM YourTable
WHERE DateCreated IN
(
	SELECT DateCreated
	FROM YourTable
	GROUP BY DateCreated
	HAVING COUNT(1) > 1
)
ORDER BY DateCreated

Open in new window

0
 
skull52Author Commented:
Thanks for the quick response Patrick, but your query is giving me results that may not be accurate, for example  2013-07-29 10:45:04.000 shows 19 occurrences for that date and time this may because I also need to match other columns, such as address state and Zip.
0
 
skull52Author Commented:
OK, so... the reason I got so many occurrences, which is accurate , is that  that if the customer is returning multiple items it generates a RA number for each and uses the same time stamp.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now