TO_DATE TO_CHAR 函数用法总结
今天在查数据时,遇到这样的问题,顺便来汇总一下遗忘的东西。
一、TO_DATE:
spl> select * from emp
where dates
between
to_date('2007-06-12 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
and
to_date('2007-06-12 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
当省略HH、MI和SS对应的输入参数时,Oracle使用0作为DEFAULT值。如果输入的日期数据忽略时间部分,Oracle会将时、分、秒部分都置为0,也就是说会取整到日。
同样,忽略了DD参数,Oracle会采用1作为日的默认值,也就是说会取整到月。
但是,不要被这种“惯性”所迷惑,如果忽略MM参数,Oracle并不会取整到年,取整到当前月。
注意:
1.在使用Oracle的to_date函数来做日期转换时,可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换,但是在Oracle中会引起错误:“ORA 01810 格式代码出现两次”。如:select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;
原因是SQL中不区分大小写,MM和mm被认为是相同的格式代码,所以Oracle的SQL采用了mi代替分钟。select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
2.另要以24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份
二、TO_CHAR( date [, fmt [, 'nlsparam'] ])
它的作用是将一个日期date转换成一个指定格式fmt的字符串。有关nlsparam基本可以不管,它是用来确定返回的日期的名字以及缩写词等的形式,如你的数据库语言如果是英语,那么返回的月份表示可能是类似这样的“MAY”;而如果你的数据库语言是汉语,那么返回的月份表示可能是类似这样的“五月”。nlsparam就是用来改变这些显示,如假设你的数据库语言是汉语,而你像把它变成英语的表示,则可以这样设置:nls_date_language=’english’。99%可以不用管这个参数。如下面这个例子:
10:13:29 SQL> select to_char(sysdate,'yyyy-mm-dd') today from dual;
找到了一些例子:
题目:数据库表A,字段commit_time存的数据是:20080301112536等。现在我要查询出今天在9:00到10:00,15:00至16:00时间段的数据。请问如何写这样的语句。
方法一:
select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd')||'0900' and commit_time <=to_char(sysdate,'yyyymmdd')||'1000'
select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd')||'1500' and commit_time <=to_char(sysdate,'yyyymmdd')||'1600'
方法二:
select *
from youtablename
where commit_time between to_char(sysdate,'yyyymmdd')||'0900' and to_char(sysdate,'yyyymmdd')||'1000'
UNION ALL
select *
from youtablename
where commit_time between to_char(sysdate,'yyyymmdd')||'1500' and to_char(sysdate,'yyyymmdd')||'1600'
方法三:
取今天 9:00 可以这样
trunc(sysdate) + to_dsinterval('0 09:00:00')
to_dsinterval 函数的作用是固定格式的字符串('day hh:mi:ss')转换成时间段。这个时间段可以和 date 数据类型作 +/- 操作
例如 select trunc(sysdate) + to_dsinterval('0 09:00:00') from dual;
此方法用在楼上 sql 语句的 between 部分就可以了。
方法四:
select * from A where substr(commit_time, 9) between '090000' and '100000' or substr(commit_time, 9) between '150000' and '160000'
如果不含后面的时间点则应该为:
select * from A where substr(commit_time, 9) between '090000' and '095959' or substr(commit_time, 9) between '150000' and '155959'
方法五:
使用 trunc(commit_time,'ss') 来查询,优化.
字段commit_time应当设定为timestamp类型,这样就方便啦