oracle between and大于,oracle between and 边界问题

oracle ：在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序SCHINESE_STROKE_M

oracle between and 边界问题

分类：

--创建一个表

create table test_hsj(

id int primary key,

num varchar2(12),

regDate date

)

select * from test_hsj;

--插入测试数据

insert into test_hsj values(1,'1', to_date('2015-05-01','yyyy-MM-dd'))

insert into test_hsj values(2,'2', to_date('2015-06-01','yyyy-MM-dd'))

insert into test_hsj values(3,'3', to_date('2015-05-11','yyyy-MM-dd'))

insert into test_hsj values(4,'4', to_date('2015-05-01','yyyy-MM-dd'))

insert into test_hsj values(5,'5', to_date('2015-06-21','yyyy-MM-dd'))

insert into test_hsj values(6,'6', to_date('2015-06-11','yyyy-MM-dd'))

insert into test_hsj values(7,'7', to_date('2016-06-11','yyyy-MM-dd'))

insert into test_hsj values(8,'8', to_date('2014-04-01','yyyy-MM-dd'))

--查询验证，日期在 5 月到6月份之间的数据 如果转换为日期的时候，只有年月的时候会默认取1号 between and 会包含两端 包含两端

--1 结论：对于日期类型 between and 包括 >= and <=

select * from test_hsj where regdate between to_date('2015-05','yyyy-MM') and to_date('2015-06','yyyy-MM')

--如果只有年月 则只会去 1号

select to_date('2015-05','yyyy-MM') from dual

--如果只有年，，那么回去当前月份 的1 号

select to_date('2014','yyyy') from dual

--2 结论： 对于数值类型 between and 等效 >= and <=

select * from test_hsj where id between 1 and 5;

--3 结论： 对于字符类型 between and 等效 >= and <=

select * from test_hsj where num between '1' and '5';

--总结论： 对于orcle数据库 between and 等效 >= and <=

上一篇oracle insert into select模板

顶

0

踩

0