SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Table1 (
    ID varchar(100),
    Name varchar(100),
    [Type] varchar(100),
    [Maintenance Time] date,
)
INSERT INTO Table1
VALUES
  ('1','EQ001','Mechanical','2019-08-19'),
  ('2','EQ001','Gear','2019-08-19'),
  ('3','EQ001','Pump','2019-08-19'),
  ('4','EQ002','Gear','2019-08-19'),
  ('5','EQ002','Pump','2019-08-19'),
  ('6','EQ003','Mechanical','2019-08-19'),
  ('7','EQ003','Mechanical','2019-08-19'),
  ('8','EQ001','Mechanical','2019-10-20'),
  ('9','EQ001','Gear','2019-10-20'),
  ('10','EQ001','Pump','2019-10-20'),
  ('11','EQ002','Gear','2019-10-20'),
  ('12','EQ002','Pump','2019-10-20'),
  ('13','EQ003','Mechanical','2019-10-20'),
  ('14','EQ003','Mechanical','2019-10-20'),
  ('15','EQ001','Mechanical','2019-12-23'),
  ('16','EQ001','Gear','2019-12-23'),
  ('17','EQ001','Pump','2019-12-23'),
  ('18','EQ002','Gear','2019-12-23'),
  ('19','EQ002','Pump','2019-12-23'),
  ('20','EQ003','Mechanical','2019-12-23'),
  ('21','EQ003','Mechanical','2019-12-23')
21 rows affected

SELECT * 
FROM Table1
WHERE DAY([Maintenance Time]) = (SELECT DAY(MAX([Maintenance Time])) FROM Table1)


ID
Name
Type
Maintenance Time


15
EQ001
Mechanical
2019-12-23

16
EQ001
Gear
2019-12-23

17
EQ001
Pump
2019-12-23

18
EQ002
Gear
2019-12-23

19
EQ002
Pump
2019-12-23

20
EQ003
Mechanical
2019-12-23

21
EQ003
Mechanical
2019-12-23

ID	Name	Type	Maintenance Time
15	EQ001	Mechanical	2019-12-23
16	EQ001	Gear	2019-12-23
17	EQ001	Pump	2019-12-23
18	EQ002	Gear	2019-12-23
19	EQ002	Pump	2019-12-23
20	EQ003	Mechanical	2019-12-23
21	EQ003	Mechanical	2019-12-23