where hiredate between to_date() and to_date() or 二楼给的写法
有可能两地方有问题: 1. 如果你数据库格式不是'yyyy-mm-dd',BETWEEN '1990-01-01' AND '1990-06-30' 应该改为: BETWEEN to_date('1990-01-01','yyyy-mm-dd') AND to_date('1990-06-30','yyyy-mm-dd' 2.如果你的hire_date为日期格式,这里也会报错,应该直接用HIRE_DATE(date和date进行比较)
有可能两地方有问题: 1. 如果你数据库格式不是'yyyy-mm-dd',BETWEEN '1990-01-01' AND '1990-06-30' 应该改为: BETWEEN to_date('1990-01-01','yyyy-mm-dd') AND to_date('1990-06-30','yyyy-mm-dd' 2.如果你的hire_date为日期格式,这里也会报错,应该直接用HIRE_DATE(date和date进行比较)
你hire_date是字符串格式的话,to_date(hire_date,'yyyy-mm-dd') between to_date('1991-01-01','yyyy-mm-dd') and to_date('1991-06-30','yyyy-mm-dd')
'1991-01-01' 明明是字符串你却要to_date你的hire_date来跟它比较
格式应该是YYYY-MM-dd,而不是YYYY-MM-DD
字符串遇到和日期或数值比较时会进行隐式转换,‘19-1月-1989’这种字符串oracle默认格式转换,这你可以想成是默认的,其他的字符串你可以给它带上格式。 BETWEEN to_date('1991-01-01','YYYY-MM-DD') AND to_date('1991-06-01','YYYY-MM-DD') BETWEEN '01-1月-1990' AND '01-6月-1990'
你的hire_date是不是时间格式啊,如果是的话,貌似你应该用to_char()
1、to_date(hire_date,'YYYY-MM-DD') 要求前提是hire_date是字符串格式的; 2、BETWEEN '1990-01-01' AND '1990-06-30' 如果hire_date是字符串,那么就是按位比较字符转asc值了; 如果hire_date是日期型要使用to_char 并且设置NLS_DATE_FORMAT=YYYY-MM-DD
where hiredate between to_date() and to_date()
1. select job_id FROM employees sd where to_char(sd.hire_date, yyyy-MM-dd) >= 1990-01-01 and to_char(sd.hire_date, yyyy-MM-dd) <= 1990-06-302. select job_id FROM employees sd where sd.hire_date > = TO_DATE(1990-01-01 00:00:00,YYYY-MM-DD HH24:MI:SS) and sd.hire_date<= TO_DATE(1990-06-30, YYYY-MM-DD HH24:MI:SS)
如按你的between....and.....书写语句,可将to_date更改为:to_char(因为between....and你是用字符串表示的日期,而不是date)现测试如下: 1.建表语句 create table tb_employees ( job_id number primary key, hire_date DATE );2.生成测试数据 insert into tb_employees select EMP_SEQ.nextval, to_date(TRUNC(DBMS_RANDOM.VALUE( to_number(to_char(to_date('1990-01-01','yyyy-mm-dd'),'J')), to_number(to_char(to_date('1990-06-30','yyyy-mm-dd')+1,'J')))),'J')+ DBMS_RANDOM.VALUE(1,3600)/3600 from dual connect by level <= 203.查询现有数据select * from tb_employees (注:可讲你的数据直接插入进表) 4.测试语句SELECT job_id FROM tb_employees WHERE to_char(hire_date,'YYYY-MM-DD') BETWEEN '1990-01-01' AND '1990-06-30' INTERSECT SELECT job_id FROM tb_employees WHERE to_char(hire_date,'YYYY-MM-DD') BETWEEN '1991-01-01' AND '1991-06-30';
hire_date 这个字段本身是DATE型的话就不应该在TO_DATE 了。
你between...and...匹配应该是字符串型,你把hire_date转换为日期格式,肯定会报类型不匹配错。如果你hire_date建表时字段定义为字符串直接 where hire_date between...and....如果是日期格式则应该where to_char(hire_date,'yyyy-mm-dd') betweent...and...
SELECT job_id FROM employees WHERE to_char(to_date(hire_date,'YYYY-MM-DD'),'yyyy-mm-dd') BETWEEN '1990-01-01' AND '1990-06-30' INTERSECT SELECT job_id FROM employees WHERE to_char(to_date(hire_date,'YYYY-MM-DD'),'yyyy-mm-dd') BETWEEN '1991-01-01' AND '1991-06-30';
数据库中的数据SELECT name FROM users WHERE birthdate BETWEEN to_date('1990-01-01','YYYY-MM-DD') AND to_date('1990-06-30','YYYY-MM-DD') INTERSECT SELECT name FROM users WHERE birthdate BETWEEN to_date('1990-04-01','YYYY-MM-DD') AND to_date('1990-08-30','YYYY-MM-DD') 执行结果
还有 between的日期格式貌似不对
or
二楼给的写法
1. 如果你数据库格式不是'yyyy-mm-dd',BETWEEN '1990-01-01' AND '1990-06-30' 应该改为:
BETWEEN to_date('1990-01-01','yyyy-mm-dd') AND to_date('1990-06-30','yyyy-mm-dd'
2.如果你的hire_date为日期格式,这里也会报错,应该直接用HIRE_DATE(date和date进行比较)
1. 如果你数据库格式不是'yyyy-mm-dd',BETWEEN '1990-01-01' AND '1990-06-30' 应该改为:
BETWEEN to_date('1990-01-01','yyyy-mm-dd') AND to_date('1990-06-30','yyyy-mm-dd'
2.如果你的hire_date为日期格式,这里也会报错,应该直接用HIRE_DATE(date和date进行比较)
你hire_date是字符串格式的话,to_date(hire_date,'yyyy-mm-dd') between to_date('1991-01-01','yyyy-mm-dd') and to_date('1991-06-30','yyyy-mm-dd')
2、BETWEEN '1990-01-01' AND '1990-06-30' 如果hire_date是字符串,那么就是按位比较字符转asc值了;
如果hire_date是日期型要使用to_char 并且设置NLS_DATE_FORMAT=YYYY-MM-DD
select job_id
FROM employees sd where to_char(sd.hire_date, yyyy-MM-dd) >= 1990-01-01
and to_char(sd.hire_date, yyyy-MM-dd) <= 1990-06-302.
select job_id
FROM employees sd where sd.hire_date
> = TO_DATE(1990-01-01 00:00:00,YYYY-MM-DD HH24:MI:SS)
and sd.hire_date<= TO_DATE(1990-06-30,
YYYY-MM-DD HH24:MI:SS)
1.建表语句
create table tb_employees
(
job_id number primary key,
hire_date DATE
);2.生成测试数据
insert into tb_employees
select EMP_SEQ.nextval,
to_date(TRUNC(DBMS_RANDOM.VALUE(
to_number(to_char(to_date('1990-01-01','yyyy-mm-dd'),'J')),
to_number(to_char(to_date('1990-06-30','yyyy-mm-dd')+1,'J')))),'J')+
DBMS_RANDOM.VALUE(1,3600)/3600
from dual
connect by level <= 203.查询现有数据select * from tb_employees (注:可讲你的数据直接插入进表)
4.测试语句SELECT job_id
FROM tb_employees
WHERE to_char(hire_date,'YYYY-MM-DD')
BETWEEN '1990-01-01' AND '1990-06-30'
INTERSECT
SELECT job_id
FROM tb_employees
WHERE to_char(hire_date,'YYYY-MM-DD')
BETWEEN '1991-01-01' AND '1991-06-30';
FROM employees
WHERE to_char(to_date(hire_date,'YYYY-MM-DD'),'yyyy-mm-dd')
BETWEEN '1990-01-01' AND '1990-06-30'
INTERSECT
SELECT job_id
FROM employees
WHERE to_char(to_date(hire_date,'YYYY-MM-DD'),'yyyy-mm-dd')
BETWEEN '1991-01-01' AND '1991-06-30';
FROM users
WHERE birthdate
BETWEEN to_date('1990-01-01','YYYY-MM-DD') AND to_date('1990-06-30','YYYY-MM-DD')
INTERSECT
SELECT name
FROM users
WHERE birthdate
BETWEEN to_date('1990-04-01','YYYY-MM-DD') AND to_date('1990-08-30','YYYY-MM-DD')
执行结果