SQL 如何将日期和时间与日期时间分开？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10000839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to separate date and time from a datetime?
提问by Rasheed Khalil Salem
I have a table with a datetime record. I need to select the record using date or time.
我有一个带有日期时间记录的表。我需要使用日期或时间选择记录。
Let's say that I have this format 1/1/2001 13:33:00in the database
假设我1/1/2001 13:33:00在数据库中 有这种格式
My question is: If I enter the date, the time or both, I should obtain the rows that have either same date or the same time. How can I achieve this?
我的问题是：如果我输入日期、时间或两者，我应该获得具有相同日期或相同时间的行。我怎样才能做到这一点？
The convert function CONVERT(VARCHAR(10), Travel.Travel_DateTime, 110)returns the dates if their time is like the default 00:00:00
CONVERT(VARCHAR(10), Travel.Travel_DateTime, 110)如果日期时间与默认时间相同，则转换函数将返回日期00:00:00
回答by Corbin
(Note that I'm assuming MySQL)
（请注意，我假设是 MySQL）
Just use the DATEand TIMEfunctions:
SELECT blah FROM tbl WHERE DATE(some_datetime_field) = '2012-04-02';
That will select any rows such that the date part of some_datetime_field is 4 Apr 2012.
这将选择任何行，使得 some_datetime_field 的日期部分是 4 Apr 2012。
The same idea applies with TIME:
同样的想法适用于 TIME：
SELECT blah FROM tbl WHERE TIME(some_datetime_field) = '14:30:00';
So, to get all rows where the date is 5 Apr 2012 or the time is 2:30 PM, you just combine the two:
因此，要获取日期为 2012 年 4 月 5 日或时间为下午 2:30 的所有行，您只需将两者结合起来：
SELECT blah FROM tbl WHERE DATE(some_datetime_field) = '2012-04-02' OR TIME(some_datetime_field) = '14:30:00';
---Edit---
- -编辑 - -
For SQL server, you should be able to use:
对于 SQL 服务器，您应该能够使用：
CONVERT(DATE, some_datetime_field) = '2012-04-02'
(From How to return the date part only from a SQL Server datetime datatype)
回答by pratik garg
say you are passing date and time in l_DATEand l_TIMEvariable ..
说你正在传递日期和时间l_DATE和l_TIME变量..
then you can use following query..
那么你可以使用以下查询..
select * from your_table
where to_char(date_field,'DD/MM/YYYY') = l_DATE
or to_char(date_field ,'HH:MI:SS') = l_TIME
If you are using Oracle database as DBMS then you can use above query and it should give you your desired answer/ result...
如果您使用 Oracle 数据库作为 DBMS，那么您可以使用上面的查询，它应该会为您提供所需的答案/结果...
