Date and time functions in ORACLE summary (reproduced)

To deal with dates in oracle Daquan

TO_DATE format
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998

24-hour format time range: 0:00:00 - 23:59:59 ....
12-hour format time range: 1:00:00 - 12:59:59 ....
1.
Date and character conversion function usage (to_date, to_char)

2.
select to_char (to_date (222, 'J'), 'Jsp') from dual

Display Two Hundred Twenty-Two

3.
Demand day of the week
select to_char (to_date ('2002-08-26 ',' yyyy-mm-dd '),' day ') from dual;
Monday
select to_char (to_date ('2002-08-26 ',' yyyy-mm-dd '),' day ',' NLS_DATE_LANGUAGE = American ') from dual;
monday
Set the date language
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
It can also be
TO_DATE ('2002-08-26 ',' YYYY-mm-dd ',' NLS_DATE_LANGUAGE = American ')

4.
The number of days between two dates
select floor (sysdate - to_date ('20020405 ',' yyyymmdd ')) from dual;

5. Time is the use of null
select id, active_date from table1
UNION
select 1, TO_DATE (null) from dual;

Note that to use TO_DATE (null)

6.
a_date between to_date ('20011201 ',' yyyymmdd ') and to_date ('20011231', 'yyyymmdd')
Then at 12 o'clock on the December 31 and December 1 after a 12-point before the scope is not included within this.
So when the time when the need for accurate, that is necessary to_char
7. Date format input format conflict depends on your installation type ORACLE character set, such as: US7ASCII, date format type is: '01-Jan-01 '
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
Or write in to_date
select to_char (to_date ('2002-08-26 ',' yyyy-mm-dd '),' day ',' NLS_DATE_LANGUAGE = American ') from dual;
Note me to cite only NLS_DATE_LANGUAGE, of course, many
To see
select * from nls_session_parameters
select * from V $ NLS_PARAMETERS

8.
select count (*)
from (select rownum-1 rnum
from all_objects
where rownum <= to_date ('2002-02-28 ',' yyyy-mm-dd ') - to_date ('2002-
02-01 ',' yyyy-mm-dd ') +1
)
where to_char (to_date ('2002-02-01 ',' yyyy-mm-dd ') + rnum-1,' D ')
not
in ('1 ', '7')

Find 2002-02-28 to 2002-02-01 Apart from the number of days on Monday and seven were in before and after the call DBMS_UTILITY.GET_TIME, so that after subtracting the results (get is 1 / 100 seconds, not milliseconds).

9.
select months_between (to_date ('01-31-1999 ',' MM-DD-YYYY '),
to_date ('12-31-1998 ',' MM-DD-YYYY ')) "MONTHS" FROM DUAL;
1

select months_between (to_date ('02-01-1999 ',' MM-DD-YYYY '),
to_date ('12-31-1998 ',' MM-DD-YYYY ')) "MONTHS" FROM DUAL;

1.03225806451613
10. Next_day usage
Next_day (date, day)

Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D

11
select to_char (sysdate, 'hh: mi: ss') TIME from all_objects
Note: The first record of the TIME and the last line is the same as a function can be established to deal with this problem
create or replace function sys_date return date is
begin
return sysdate;
end;

select to_char (sys_date, 'hh: mi: ss') from all_objects;
12.
Access hours

SELECT EXTRACT (HOUR FROM TIMESTAMP '2001-02-16 2:38:40 ') from offer
SQL> select sysdate, to_char (sysdate, 'hh') from dual;

SYSDATE TO_CHAR (SYSDATE, 'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07

SQL> select sysdate, to_char (sysdate, 'hh24') from dual;

SYSDATE TO_CHAR (SYSDATE, 'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19

Day for years and is similar
13.
Years on treatment
select older_date,
newer_date,
years,
months,
abs (
trunc (
newer_date-
add_months (older_date, years * 12 + months)
)
) Days
from (select
trunc (months_between (newer_date, older_date) / 12) YEARS,
mod (trunc (months_between (newer_date, older_date)),
12) MONTHS,
newer_date,
older_date
from (select hiredate older_date,
add_months (hiredate, rownum) + rownum newer_date
from emp)
)

14.
Processing means the month variable number of days
select to_char (add_months (last_day (sysdate) +1, -2), 'yyyymmdd'), last_day (sysdate) from dual

16.
Find out the number of days this year
select add_months (trunc (sysdate, 'year'), 12) - trunc (sysdate, 'year') from dual

Handling of leap year
to_char (last_day (to_date ('02 '| |: year,' mmyyyy ')),' dd ')
If 28 is not a leap year

17.
the difference between yyyy and rrrr
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001

18. Do not deal with time zones
select to_char (NEW_TIME (sysdate, 'GMT', 'EST'), 'dd / mm / yyyy hh: mi: ss'), sysdate
from dual;

19.
An interval of 5 seconds
Select TO_DATE (FLOOR (TO_CHAR (sysdate, 'SSSSS') / 300) * 300, 'SSSSS'), TO_CHAR (sysdate, 'SSSSS')
from dual

2002-11-1 9:55:00 35786
SSSSS that 5 seconds

20.
The first few days a year
select TO_CHAR (SYSDATE, 'DDD'), sysdate from dual
3102002-11-6 10:03:51

21. Calculate hours, minutes, seconds, milliseconds
select
Days,
A,
TRUNC (A * 24) Hours,
TRUNC (A * 24 * 60 - 60 * TRUNC (A * 24)) Minutes,
TRUNC (A * 24 * 60 * 60 - 60 * TRUNC (A * 24 * 60)) Seconds,
TRUNC (A * 24 * 60 * 60 * 100 - 100 * TRUNC (A * 24 * 60 * 60)) mSeconds
from
(
select
trunc (sysdate) Days,
sysdate - trunc (sysdate) A
from dual
)

select * from tabname
order by decode (mode, 'FIFO', 1, -1) * to_char (rq, 'yyyymmddhh24miss');

/ /
floor ((date2-date1) / 365) as in
floor ((date2-date1, 365) / 30) as the month
mod (mod (date2-date1, 365), 30) as the date.
23.next_day function
next_day (sysdate, 6) is from the current starting next Friday. The figures behind the counting from Sunday onwards.
1234567
Day 123 456

There are a lot of oracle date function

In oracle there are many on the date of the function, such as:
1, add_months () from a date value for the increase or decrease the number of months
date_value: = add_months (date_value, number_of_months)
Example:
SQL> select add_months (sysdate, 12) "Next Year" from dual;

Next Year
----------
13-11 months -04

SQL> select add_months (sysdate, 112) "Last Year" from dual;

Last Year
----------
13-3 -13 months

SQL>

2, current_date () returns the current time zone will put the current date
date_value: = current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone, current_date from dual;

SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11 -03 months

SQL> alter session set time_zone = '-11:00'
2 /

Conversation has changed.

SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------- -
-11:00 Pm -11 -03 04.59.13.668000 from December to November:
00

SQL>

3, current_timestamp () to timestamp with time zone data type returns the current time zone will put the current date
timestamp_with_time_zone_value: = current_timestamp ([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------- -
+08:00 AM +08 -03 11.56.28.160000 13-11 months:
00

SQL> alter session set time_zone = '-11:00'
2 /

Conversation has changed.

SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------- -
-11:00 Pm -11 -03 04.58.00.243000 from December to November:
00

SQL>

4, dbtimezone () returns the time zone
varchar_value: = dbtimezone
SQL> select dbtimezone from dual;

DBTIME
------
-07:00

SQL>

5, extract () to find a date or interval value of the field values
date_value: = extract (date_field from [datetime_value | interval_value])
SQL> select extract (month from sysdate) "This Month" from dual;

This Month
----------
11

SQL> select extract (year from add_months (sysdate, 36)) "3 Years Out" from dual;

3 Years Out
-----------
2006

SQL>

6, last_day () Returns the date parameters of the date of the last day of the month
date_value: = last_day (date_value)
SQL> select last_day (date'2000-02-01 ') "Leap Yr?" From dual;

Leap Yr?
----------
29-2 -00 months

SQL> select last_day (sysdate) "Last day of this month" from dual;

Last day o
----------
30-11 months -03

SQL>

7, localtimestamp () to return the date and time of session
timestamp_value: = localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
----------------------------
13-11 months -03 12.09.15.433000
Afternoon

SQL> select localtimestamp, current_timestamp from dual;

LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ---------------------- --------------
-03 12.09.31.006000 13-11 months 13-11 months at +08 -03 12.09.31.006000:
00 PM

SQL> alter session set time_zone = '-11:00';

Conversation has changed.

SQL> select localtimestamp, to_char (sysdate, 'DD-MM-YYYY HH: MI: SS AM') "SYSDATE" from dual;

LOCALTIMESTAMP SYSDATE
---------------------------- ---------------------- -
December-November -03 05.11.31.259000 13-11-2003 12:11:31 PM PM

SQL>

8, months_between () determine the number of months between two dates
number_value: = months_between (date_value, date_value)
SQL> select months_between (sysdate, date'1971-05-18 ') from dual;

MONTHS_BETWEEN (SYSDATE, DATE'1971-05-18 ')
----------------------------------------
389.855143

SQL> select months_between (sysdate, date'2001-01-01 ') from dual;

MONTHS_BETWEEN (SYSDATE, DATE'2001-01-01 ')
----------------------------------------
34.4035409

SQL>

9, next_day () to set a date value and return by the second argument that the days of the date of the first in value (to be returned to the corresponding day of the name string)

Relevant date and week functions

1. Query on the first day of a week
select trunc (decode (ww, 53, to_date (yy | | '3112 ',' yyyyddmm '), to_date (yy | |' - '| | to_char (ww * 7),' yyyy-ddd ')),' d ') last_day
from (select substr ('2004-32 ', 1, 4) yy, to_number (substr ('2004-32', 6)) ww
from dual)

select trunc (to_date (substr ('2003-01 ', 1,5) | | to_char ((to_number (substr ('2003-01', 6))) * 7), 'yyyy-ddd'), 'd' ) -6 first_day from dual

select min (v_date) from
(Select (to_date ('200201 ',' yyyymm ') + rownum) v_date
from all_tables
where rownum <370)
where to_char (v_date, 'yyyy-iw') = '2002-49 '

2. Check the last day of a week
select trunc (decode (ww, 53, to_date (yy | | '3112 ',' yyyyddmm '), to_date (yy | |' - '| | to_char (ww * 7),' yyyy-ddd ')),' d ') - 6 first_day
from (select substr ('2004-33 ', 1, 4) yy, to_number (substr ('2004-33', 6)) ww
from dual)

select trunc (to_date (substr ('2003-01 ', 1,5) | | to_char ((to_number (substr ('2003-01', 6))) * 7), 'yyyy-ddd'), 'd' ) last_day from dual

select max (v_date) from
(Select (to_date ('200408 ',' yyyymm ') + rownum) v_date
from all_tables
where rownum <370)
where to_char (v_date, 'yyyy-iw') = '2004-33 '

3. Check the date a week
select min_date, to_char (min_date, 'day') day from
(Select to_date (substr ('2004-33 ', 1,4) | | '001' + rownum-1, 'yyyyddd') min_date
from all_tables
where rownum <= decode (mod (to_number (substr ('2004-33 ', 1,4)), 4), 0,366,365)
union

select to_date (substr ('2004-33 ', 1,4) -1 | |
decode (mod (to_number (substr ('2004-33 ', 1,4)) -1,4), 0,359,358) + rownum,' yyyyddd ') min_date
from all_tables
where rownum <= 7
union

select to_date (substr ('2004-33 ', 1,4) +1 | | '001' + rownum-1, 'yyyyddd') min_date
from all_tables
where rownum <= 7
)
where to_char (min_date, 'yyyy-iw') = '2004-33 '

oracle in the computation time

Forum often see questions on the oracle problem in the time of operation, and today have the time, looked at the brothers before you post, finishing a little, and made a sample, which I think may be helpful.
Thank ern, eric.li and the moderators are enthusiastic brothers

As follows:
1, oracle date operations support
2, the date when the operation is carried out in days
3, when the need for smaller units such as minutes and seconds count value, by the time you can convert hex
4, timing hex conversion attention brackets (see example in red brackets), otherwise it will go wrong

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh: mi: ss';

Conversation has changed.

SQL> set serverout on
SQL> declare
2 DateValue date;
3 begin
4 select sysdate into DateValue from dual;
5 dbms_output.put_line ('source of time:' | | to_char (DateValue));
6 dbms_output.put_line ('Source of time by one day:' | | to_char (DateValue-1));
7 dbms_output.put_line ('source time minus 1 day 1 hour:' | | to_char (DateValue-1-1/24));
8 dbms_output.put_line ('source time minus 1 day 1 hour 1 minute:' | | to_char (DateValue-1-1/24-1 / (24 * 60)));
9 dbms_output.put_line ('source time minus 1 day 1 hour 1 minute 1 second:' | | to_char (DateValue-1-1/24-1 / (24 * 60) -1 / (24 * 60 * 6
0)));
10 end;
11 /
Source Time :2003-12-29 11:53:41
Source of time by one day :2003-12-28 11:53:41
Source time minus 1 day 1 hour :2003-12-28 10:53:41
Source time minus 1 day 1 hour 1 minute :2003-12-28 10:52:41
Source time minus 1 day 1 hour 1 minute 1 second :2003-12-28 10:52:40

PL / SQL procedure successfully completed.

分类:Database 时间:2010-03-03 人气:318
分享到:
blog comments powered by Disqus

相关文章

  • Oracle - 'yyyy-mm-dd' & 'yyyymmdd' 2011-09-16

    the oracle date format 'yyyy-mm-dd' and 'yyyymmdd' difference Of Yunianyueri the "day" is a bit, to deal with not the same, 'yyyymmdd' format, no problem, and type 'yyyy-mm-dd' format is not, see: SQL> alt

  • oracle alter system command 2010-05-21

    We can use the alter system statement in the database shame ^ Bitis modify the course of initialization parameter. If the instance at boot time using a text initialization parameter file, so we modify the statement in the use of alter system initiali ...

  • Alter system in Oracle 2011-08-07

    Alter system archive log [start | stop | all |...] alter system archive log all; alter system archive log next; alter system archive log sequence 104; alter system archive log current; alter system archive log current noswitch; The following command

  • TO_CHAR (date conversion) Function In Oracle 2010-05-26

    SimpleDateFormat small mind a bit before learning experience, because the database will use the date format, and the two "Date Format Specifiers - Date formatting definition of" slightly different, so check the information for later Handboo ...

  • Java always hurt - China December 31, 1927 at midnight 2011-08-15

    Words in Chinese December 31, 1927 at midnight a magical thing happened, the Chinese instant in time by 5 minutes 52 seconds Believe it or not, anyway, I believe it! Do not believe the code you see below! /* * Licensed to the Apache Software Foundati

  • 匹配yyyy-mm-dd日期格式的的正则表达式 2013-12-01

    今天头让我修改个javascript方法,验证输入的日期是否符合要求。恩。我们的要求是yyyy-mm-dd这样的格式,其他的统统不符合要求。原来的方法没用正则表达式,用了一堆判断。 其实我也很头疼正则表达式,百度了一些资料,总结一下,这里留个备份吧。 要考虑的问题:合法的日期是多少;每个月的天数不一样;闰年的问题。。。。 1、合法的日期:MSDN上规定--在公元(基督纪元)0001 年 1 月 1 日午夜 12:00:00 到公元 (C.E.) 9999 年 12 月 31 日晚上 11:59:

  • js字符串日期yyyy-MM-dd转化为date示例代码 2014-08-16

    获取表单中的日期往后台通过json方式传的时候,遇到Date.parse(str)函数在ff下报错,有类似情况的朋友可以参考下本文 最近遇到一个问题,就是获取表单中的日期往后台通过json方式传的时候,遇到Date.parse(str)函数在ff下报错: NAN 找了些资料,发现是由于Date.parse()函数对日期格式有要求:详细参考 Date.parse函数 对于js操作日期: 创建一个日期对象: var objDate=new Date([arguments list]); 参数形式有以

  • To "yyyy-mm-dd" String-based to the same format of Date-based approach 2010-03-29

    The project should pass over time to form and databases to be able to compare the size of DateTime type field in the form, checking a lot of ways, most of them say, use the SimpleDateFormat conversion, but the results always turn out like the FRI Jan ...

  • JS determine input box enter the date format is yyyy-mm-dd and the correct date 2010-05-10

    function IsDate(sm,mystring) { var reg = /^(\d{4})-(\d{2})-(\d{2})$/; var str = mystring; var arr = reg.exec(str); if (str=="") return true; if (!reg.test(str)&&RegExp.$2<=12&&RegExp.$3<=31){ alert(" Please ensure that "+ sm +", ente

  • alter system switch logfile and alter system archive log current difference 2010-09-18

    1 alter system switch logfile is forced log switch, not necessarily the current redo log file archiving (If the file opens automatically on archived redo log before, if automatic archiving is not turned on, not the current redo log file.) alter syste

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.321 (s). 14 q(s)