TO_DATE格式
Day:
dd number
dy abbreviatedfri
dayspelled outfriday
ddspth spelled out ordinal twelfth
Month:
mm number
monabbreviatedmar
monthspelled outmarch
Year:
yy twodigits
yyyy fourdigits
小时格式下时间范围为 :: ::
小时格式下时间范围为 :: ::
日期和字符转换函数用法(to_dateto_char)
select to_char( to_date(J)Jsp)from dual
显示Two Hundred TwentyTwo
求某天是星期几
select to_char(to_date(yyyymmdd)day) from dual;
星期一
select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
也可以这样
TO_DATE ( YYYYmmdd NLS_DATE_LANGUAGE = American)
两个日期间的天数
select floor(sysdate to_date(yyyymmdd)) from dual;
时间为null的用法
select id active_date from table
UNION
select TO_DATE(null) from dual;
注意要用TO_DATE(null)
a_date between to_date(yyyymmdd) and to_date(yyyymmdd)
那么月号中午点之后和月号的点之前是不包含在这个范围之内的
所以当时间需要精确的时候觉得to_char还是必要的
日期格式沖突问题
输入的格式要看你安装的ORACLE字符集的类型 比如: USASCII date格式的类型就是: Jan
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;
注意我这只是举了NLS_DATE_LANGUAGE当然还有很多
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
日期和字符转换函数用法(to_dateto_char)
We overwrite NLS_DATE_FORMAT into different formats for the session
SQL> alter session set nls_date_format = DDMONRR; Session altered
SQL> set pagesize
SQL> set linesize
SQL> select * from nls_session_parameters;
NLS_LANGUAGE AMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMATDDMONRR
NLS_DATE_LANGUAGEAMERICAN
NLS_SORT BINARY
NLS_TIME_FORMATHHMISSXFF AM
NLS_TIMESTAMP_FORMAT DDMONRR HHMISSXFF AM
NLS_TIME_TZ_FORMAT HHMISSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMATDDMONRR HHMISSXFF AM TZH:TZM
NLS_DUAL_CURRENCY$
NLS_COMP BINARY
rows selected
specify it in SQL statement:
SQL> select to_date(SEPDDMONYYYY) from dual;
SEP
SQL> alter session set nls_date_format = MMDDYYYY;
Session altered
SQL> select to_date(SEPDDMONYY) from dual;
TO_DATE(
SQL> alter session set nls_date_format = RRRRMMDD;
Session altered
SQL> select to_date(SEPDDMONYYYY) from dual;
TO_DATE(
When we use TO_CHAR function we get expected results of format fromcurrent SQL statement:
SQL> alter session set nls_date_format = MMDDYY;
Session altered
SQL> select to_char(sysdateddmmyyyy) from dual;
TO_CHAR(SYSDATEDDMMYYYY)
SQL> alter session set nls_date_format = RRMONDD;
Session altered
SQL> select to_char(sysdateddmonyy) from dual;
TO_CHAR(SYSDATEDDMONYY)
sep
SQL> select to_char(sysdateddMonyy) from dual;
TO_CHAR(SYSDATEDDMONYY)
Sep
select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date(yyyymmdd) to_date(
yyyymmdd)+
)
where to_char( to_date(yyyymmdd)+rnum D )
not
in ( )
查找至间除星期一和七的天数
在前后分别调用DBMS_UTILITYGET_TIME 让后将结果相减(得到的是/秒 而不是毫秒)
select months_between(to_date(MMDDYYYY)
to_date(MMDDYYYY)) MONTHS FROM DUAL;
select months_between(to_date(MMDDYYYY)
to_date(MMDDYYYY)) MONTHS FROM DUAL;
Next_day的用法
Next_day(date day)
MondaySunday for format code DAY
MonSun for format code DY
for format code D
select to_char(sysdatehh:mi:ss) TIME from all_objects
注意第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_datehh:mi:ss) from all_objects;
获得小时数
SELECT EXTRACT(HOUR FROM TIMESTAMP ::) from offer
SQL> select sysdate to_char(sysdatehh)from dual;
SYSDATETO_CHAR(SYSDATEHH)
::
SQL> select sysdate to_char(sysdatehh)from dual;
SYSDATETO_CHAR(SYSDATEHH)
::
获取年月日与此类似
年月日的处理
select older_date
newer_date
years
months
abs(
trunc(
newer_date
add_months( older_dateyears*+months )
)
) days
from ( select
trunc(months_between( newer_date older_date )/) YEARS
mod(tr