查找所有日期列中时间戳为00:00:00.000的行。

Question

我有一个表，其中一列是registrationDate，其类型为datetime。我需要找到所有registrationDate为时间戳00:00:00.000的行。例如：

registrationDate: '2019-03-20 00:00:00.000'

我需要查询类似以下内容的东西：

select * from table where registrationDate like '%00:00:00.000';

- Ishant Gaurav

---

请不要将日期时间值视为字符串。它们不是字符串，而且LIKE操作符不起作用（我相信你已经尝试过了）。你所需要的是具有午夜时间组成部分的数据。 - Aaron Bertrand

（我在这篇文章中谈到了这个问题。） - Aaron Bertrand

Answer 1

您可以将datetime转换为time：

WHERE CAST(registrationDate AS TIME) = '00:00'

- Salman A

---

@GeorgeMenoutis 你不能使用0。什么是0时间？试试看。无论你怎么做，它都不会转换。 - Sean Lange

@Sean Lange 我在考虑底层数据类型。这个：select case when convert(datetime,'19000101')=0 then 1 else 0 end 返回1。但是在看了你的评论之后，我检查了转换表格并注意到尽管从数字数据类型到 datetime 的转换可以显式和隐式地进行，但是转换为 time 根本不可能。每天都有新的东西可学。 - George Menoutis

是的，日期和时间数据类型的隐式转换方式非常奇怪。 - Sean Lange

区别在于旧类型(datetime/smalldatetime)和2008年引入的新类型(date/time/datetime2/datetimeoffset)之间。后者继承了很少的隐式行为（最常见的陷阱是getdate()+1与sysdatetime()+1）。 - Aaron Bertrand

Answer 2

WHERE registrationDate = CONVERT(date, registrationDate);

- Aaron Bertrand

---

@Ishant 不理解问题。你能换个说法吗？ - Aaron Bertrand

你试过我发布的内容了吗？还是只是想象它可能会起作用？日期时间列不是字符串 - 这只是像Management Studio这样的工具向您呈现的方式。 LIKE 不会按照您想象的方式工作。 - Aaron Bertrand

如果X的类型为DATETIME，则CONVERT(date,X)将把它转换为类型DATE，这会移除时间部分。然后将其与原始的X进行比较。日期将通过添加00:00:00.000时间重新转换为DATETIME。如果结果相等，则原始部分必须是00:00:00.000时间。 - David Dubois

@DavidDubois date的优先级比datetime低。https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017。在Aaron的表达式中，`CONVERT(date, registrationDate)将被隐式转换回datetime，但是时间将变为00:00:00.000`，因为它已经“丢失”。 - Thom A

虽然它不完全符合这种用例，但是一些实际性能测试表明此转换是从日期时间中删除时间的最有效方法，并且这里有一个后续跟进。 - Aaron Bertrand

Answer 3

请看下面的更新。

Create Table #tbl
(
registrationDate Datetime
)
Insert Into #tbl Values
('2019-03-20 00:00:00.000'),
('2019-03-20 00:00:25.000')

查询

Select * From #tbl
Where Cast(registrationDate As Time) = '00:00:00.0000000'

结果：

registrationDate
2019-03-20 00:00:00.000

更新：如果你真的需要使用“Like”

Select * From #tbl
Where convert(VarChar(50), registrationDate, 121) Like '%00:00:00.000'

- level3looper

Answer 4

select * from table where FORMAT(registrationDate, 'HH:mm:ss:ms') ='00:00:00.000'

- TKAY

---

FORMAT() 实际上是一个相当糟糕的想法（尤其是作为整个表扫描的一部分），详见此链接。 - Aaron Bertrand

Answer 5

以下表达式可以获取每个日期时间值对应的带有时间部分的日期：

dateadd(day, datediff(day, 0, '<date_time>'), 0)

因此，以下代码将能完成工作：

create table foo (id int, registrationDate datetime)
insert foo values
   (1, '2019-04-04T03:22:48.00'),
   (2, '2019-04-04T00:00:00.00')
select * from foo
where registrationDate =
      dateadd(day, datediff(day, 0, registrationDate), 0)

- hanspo