?
Solved

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

Posted on 2015-01-13
4
Medium Priority
?
88 Views
Last Modified: 2015-01-27
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
Comment
Question by:skull52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 40547036
To find the duplicates...

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

Open in new window

0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 40547054
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
 

Author Comment

by:skull52
ID: 40547063
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
 

Author Comment

by:skull52
ID: 40547098
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question