Oracle:带时间戳的SQL选择日期 [英] Oracle: SQL select date with timestamp
本文介绍了Oracle:带时间戳的SQL选择日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下数据:
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
我正在尝试编写一个sql
查询,该查询选择与某个日期匹配的所有记录,但是我正在对BK_DATE
字段使用时间戳记,无论我尝试什么,我的查询都不会产生结果.
I am trying to write a sql
query that selects all records that matches a certain date, however I am using timestamp for the BK_DATE
field and no matter what I try it my query yields no results.
SQL :我已经尝试了以下查询,但没有任何结果
SQL: I have tried the following queries but they yield no results
1.
select *
from booking_session
where bk_date = to_date('18-03-2012', 'dd-mm-yyyy');
2.
select *
from booking_session
where bk_date = to_timestamp('18-03-2012', 'dd-mm-yyyy');
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
);
推荐答案
尼古拉斯·克拉斯诺夫(Nicholas Krasnov)提供的答案
Answer provided by Nicholas Krasnov
SELECT *
FROM BOOKING_SESSION
WHERE TO_CHAR(T_SESSION_DATETIME, 'DD-MM-YYYY') ='20-03-2012';
这篇关于Oracle:带时间戳的SQL选择日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文