Oracle日期比较的问题，怎么比较日期，用了好多种方法都达不到效果

Question

CREATE TABLE student1(
stuID varchar2(10) primary key,
stuName varchar2(10) NOT NULL ,
major varchar2(50) NOT NULL ) ;
CREATE TABLE book (
BID varchar2(10) primary key,
title varchar2(50) NOT NULL,
author varchar2(20) NOT NULL ) ;
CREATE TABLE borrow (
borrowID varchar2(10) primary key,
stuID varchar2(10),
BID varchar2(10),
T_time date NOT NULL,
B_time date );

alter table borrow modify(B_time to_char(date,'yyyy-MM-DD') );
INSERT INTO student1(stuID,stuName,major)VALUES('1001','林林','计算机');
INSERT INTO student1(stuID,stuName,major)VALUES('1002','白杨','计算机');
INSERT INTO student1(stuID,stuName,major)VALUES('1003','虎子','英语');
INSERT INTO student1(stuID,stuName,major)VALUES('1004','北漂的雪','工商管理');
INSERT INTO student1(stuID,stuName,major)VALUES('1005','五月','数学');

INSERT INTO book(BID,title,author)VALUES('B001','人生若只如初见','安意如');
INSERT INTO book(BID,title,author)VALUES('B002','入学那天遇见你','晴空');
INSERT INTO book(BID,title,author)VALUES('B003','感谢折磨你的人','如娜');
INSERT INTO book(BID,title,author)VALUES('B004','我不是教你诈','刘庸');
INSERT INTO book(BID,title,author)VALUES('B005','英语四级','白雪');

INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12月-26',null);
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','08-1月-05',null) ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T003','1005','B001','07-10月-08','07-12月-25');
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','07-12月-16','08-1月-7') ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004','07-12月-22',null) ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','08-1月-06',null) ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','07-9月-11',null) ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','07-12月-10',null) ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','07-10月-16','07-12月-18');
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T010','1002','B002','07-9月-15','08-1月-5') ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','07-12月-28',null) ;
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','1002','B003','07-12月-30',null);

select t.stuid,t.stuname,k.bid,k.title,w.t_time from student1 t join borrow w on (t.stuid=w.stuid)
join book k on (w.bid=k.bid)
where t.major='计算机' and
to_char(t_time,'YYYY-MM-DD')>'2007-12-15'and to_char(t_time,'YY-MM-DD')<'2008-1-8';
在线等

Answer 1

to_char是转化成字符了，这样就没法比较了啊，要是试下export NLS_DATE_FORMAT='YYYY-MM-DD' 这样就不需要转化了

貌似有个to_date是给日期用的，应该是可以的

select t.stuid,t.stuname,k.bid,k.title,w.t_time from student1 t join borrow w on (t.stuid=w.stuid)
join book k on (w.bid=k.bid)
where t.major='计算机' and
t_time>to_date('2007-12-15','YYYY-MM-DD')and t_time<to_date('2008-1-8','YYYY-MM-DD');追问

可以把整条语句完整的写一下吗？

追答

select t.stuid,t.stuname,k.bid,k.title,w.t_time from student1 t join borrow w on (t.stuid=w.stuid)
join book k on (w.bid=k.bid)
where t.major='计算机' and
t_time>to_date('2007-12-15','YYYY-MM-DD')and t_time<to_date('2008-1-8','YYYY-MM-DD');

追问

我测试了一下，还是一条数据查不出来，麻烦您再给看看，有没有其他的方法了

追答

不是我写的有问题是你前面插入的数据有问题，你插入的时候没有报错么？我改了下                        INSERT  INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','1002','B003',to_date('2007-12-30','YYYY-MM-DD'),null);

本回答被提问者和网友采纳