Display only the rows where the date is the same

Please use like below , Using row_number function...

SELECT * FROM
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY ACQ_DATE ORDER BY ACQ_DATE DESC) rnk
FROM SSG_ADRPT
)r
WHERE rnk = 1

or use..
COUNT(*) OVER (PARTITION BY.....)

SELECT * FROM
(
    SELECT * , COUNT(*) OVER (PARTITION BY ACQ_DATE) rnk
    FROM SSG_ADRPT
)r
WHERE rnk >= 1  

/* You can adjust this clause ..... if you want records where we have multiple dates then use rnk > 1 */
/* You can adjust this clause ..... if you want records where we have 1 DATE or multiple dates  then use rnk >= 1 */

What datatype is your ACQ_DATE and what granularity are you after - same day, same hour, etc...

Also is there some other boundary to dupe dates (like same account or product or entry type ot thing) ?

Do you want to see every row instance of the duplicate date?

sample trail for you -

--

CREATE TABLE SSG_ADRPT
(
	 ID INT
	,ACQ_DATE DATE
)
GO

INSERT INTO SSG_ADRPT VALUES ( 1,GETDATE() ) ,( 2, GETDATE() ) , ( 3,GETDATE() ) , (4,GETDATE()+1 ),(5,GETDATE()+1) , ( 6,GETDATE() )

INSERT INTO SSG_ADRPT VALUES ( 7, GETDATE() - 2 )

SELECT Id,ACQ_DATE FROM 
(
    SELECT * , COUNT(*) OVER (PARTITION BY ACQ_DATE) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1  

Select allOpen in new window

OUTPUT

/*------------------------

SELECT Id,ACQ_DATE FROM 
(
    SELECT * , COUNT(*) OVER (PARTITION BY ACQ_DATE) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1  
------------------------*/
Id          ACQ_DATE
----------- ----------
6           2017-12-08
1           2017-12-08
2           2017-12-08
3           2017-12-08
4           2017-12-09
5           2017-12-09

(6 row(s) affected)

Select allOpen in new window

we can also use simple GROUP BY and  HAVING Clause to get duplicate date date . Sample below-

--

SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY ACQ_DATE
	HAVING COUNT(*) > 1
)k ON a.ACQ_DATE = k.ACQ_DATE

Select allOpen in new window

OUTPUT

/*------------------------
SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY ACQ_DATE
	HAVING COUNT(*) > 1
)k ON a.ACQ_DATE = k.ACQ_DATE
------------------------*/
ID          ACQ_DATE
----------- ----------
6           2017-12-08
1           2017-12-08
2           2017-12-08
3           2017-12-08
4           2017-12-09
5           2017-12-09

(6 row(s) affected)

Select allOpen in new window

@Pawan,

Mate, I have asked a couple of questions above and would like to get an answer, your proliferation of posts might obfuscate my questions....

A set of sample data and your expected output would be most helpful in providing a solution.

Mark, the datatype is datetime, and the date is associated with a serial number and several other columns and I want to display the rows
 where the date is the same for that serial number.

As Mark wills requested, we still need to know the granularity required for defining the same date. For example, is an acq_date of 12/11/2017 2:38:09 PM considered to be the same date as 12/11/2017 3:06:23 PM since they are the same day (and hour, etc.) or must they have the exact same date and time period?

Hi Mark - I have already proposed the same solution long back. :) [ #a42396044 , #a42396083 ]

@Author - Have you tried my last solutions I gave?

Please try the 2 full tested solution below-

Explanation SOLUTION 1 - First I am removing the time part from the ACQ_DATE, then I am counting how many same rows we have for
each ACQ_DATE without time and then I am picking all the rows which are greater than 1 meaning they have duplicates.

Explanation SOLUTION 2 - In the second solution I have group by the data using ACQ_DATE without time and pick data where the dates count is greater than 1. After that JOIN this result set with the original data to fetch the required (all) columns.

If you want to include the Time also in the calculations then we can directly use this COUNT(*) OVER (PARTITION BY ACQ_DATE)

DATA GENERATION AND TABLE CREATION

--

CREATE TABLE SSG_ADRPT
(
	 ID INT
	,ACQ_DATE DATETIME
)
GO

INSERT INTO SSG_ADRPT VALUES ( 1,GETDATE() ) ;
INSERT INTO SSG_ADRPT VALUES ( 2, GETDATE() ) ;
INSERT INTO SSG_ADRPT VALUES ( 3,GETDATE() ) ;
INSERT INTO SSG_ADRPT VALUES (4,GETDATE()+1 ) ;
INSERT INTO SSG_ADRPT VALUES (5,GETDATE()+1) ;
INSERT INTO SSG_ADRPT VALUES ( 6,GETDATE() ) ;

INSERT INTO SSG_ADRPT VALUES ( 7, GETDATE() - 2 )

Select allOpen in new window

SOLUTION 1

--

SELECT Id,ACQ_DATE FROM 
(
    SELECT *, COUNT(*) OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1  

--

Select allOpen in new window

OUTPUT

/*------------------------

SELECT Id,ACQ_DATE FROM 
(
    SELECT *, COUNT(*) OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1 
------------------------*/
Id          ACQ_DATE
----------- -----------------------
6           2017-12-12 04:44:13.443
2           2017-12-12 04:44:17.933
1           2017-12-12 04:44:03.677
3           2017-12-12 04:44:31.223
5           2017-12-13 04:44:22.780
4           2017-12-13 04:44:27.567

(6 row(s) affected)

Select allOpen in new window

OR

SOLUTION 2

--

SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0) ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)
	HAVING COUNT(*) > 1
)k ON DATEADD(dd, DATEDIFF(dd, 0,a.ACQ_DATE), 0) = k.ACQ_DATE

--

Select allOpen in new window

OUTPUT

--

/*------------------------

SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0) ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)
	HAVING COUNT(*) > 1
)k ON DATEADD(dd, DATEDIFF(dd, 0,a.ACQ_DATE), 0) = k.ACQ_DATE
------------------------*/
ID          ACQ_DATE
----------- -----------------------
6           2017-12-12 04:44:13.443
2           2017-12-12 04:44:17.933
1           2017-12-12 04:44:03.677
3           2017-12-12 04:44:31.223
5           2017-12-13 04:44:22.780
4           2017-12-13 04:44:27.567

(6 row(s) affected)

--

Select allOpen in new window

Yes I am only interested in the date part, I will try both Marks, and Pawans Solutions.