Oracle：SQL 选择带时间戳的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13337635/
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
Oracle: SQL select date with timestamp
提问by David Garcia
I have the following data:
我有以下数据：
SQL> select * from booking_session;
BK_ID|BK_DATE
-----|-------------------------
1|18-MAR-12 10.00.00.000000
2|18-MAR-12 10.25.00.000000
3|18-MAR-12 10.30.00.000000
4|18-MAR-12 10.35.00.000000
5|18-MAR-12 10.40.00.000000
I am trying to write a sqlquery that selects all records that matches a certain date, however I am using timestamp for the BK_DATEfield and no matter what I try it my query yields no results.
我正在尝试编写一个sql查询来选择与某个日期匹配的所有记录，但是我正在为该BK_DATE字段使用时间戳，无论我尝试什么，我的查询都不会产生任何结果。
SQL: I have tried the following queries but they yield no results
SQL：我尝试了以下查询，但没有产生任何结果
1.
1.
select *
from booking_session
where bk_date = to_date('18-03-2012', 'dd-mm-yyyy');
2.
2.
select *
from booking_session
where bk_date = to_timestamp('18-03-2012', 'dd-mm-yyyy');
3.
3.
select *
from booking_session
where bk_date = to_date('18-MAR-2012', 'dd-mm-yyyy');
It only works when I set the whole date value like;
它仅在我设置整个日期值时才有效；
select *
from booking_session
WHERE bk_date = '18-MAR-12 11.00.00.000000';
CREATE TABLE BOOKING_SESSION(
BK_ID NUMBER NOT NULL,
BK_DATE TIMESTAMP,
BK_BOOKER NUMBER,
BK_CUSTOMER NUMBER,
BK_TREATMENT NUMBER,
T_SESSION_DATETIME TIMESTAMP,
STAFFAPPOINTED NUMBER,
BK_ROOM NUMBER
);
INSERT INTO BOOKING_SESSION VALUES (
1,
TO_TIMESTAMP('18/03/2012 10:00', 'DD/MM/YYYY HH24:MI'),
1,
1,
1,
TO_TIMESTAMP('20/03/2012 11:00', 'DD/MM/YYYY HH24:MI'),2,1
);
回答by n00b
You can specify the whole day by doing a range, like so:
您可以通过执行范围来指定一整天，如下所示：
WHERE bk_date >= TO_DATE('2012-03-18', 'YYYY-MM-DD')
AND bk_date < TO_DATE('2012-03-19', 'YYYY-MM-DD')
More simply you can use TRUNC:
更简单的是，您可以使用 TRUNC：
WHERE TRUNC(bk_date) = TO_DATE('2012-03-18', 'YYYY-MM-DD')
TRUNC without parameter removes hours, minutes and seconds from a DATE.
不带参数的 TRUNC 从 DATE 中删除小时、分钟和秒。
回答by David Garcia
Answer provided by Nicholas Krasnov
尼古拉斯·克拉斯诺夫提供的答案
SELECT *
FROM BOOKING_SESSION
WHERE TO_CHAR(T_SESSION_DATETIME, 'DD-MM-YYYY') ='20-03-2012';
