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 人气:326
分享到:
blog comments powered by Disqus

相关文章

  • 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

  • Oracle DBA之手动注册监听(listener) -- alter system set local_listener="T02"; 2012-03-09

    记录下刚刚做的一个为一个数据库(t02)配置多个监听(listener)的实验,过程有点小曲折. (1)新增两个测试的监听,listener.ora的配置内容(可纯手动编辑该文件或使用netca)如下: (LISTENER为系统原有:L1.L2为测试用新增的监听) # listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora # Generated by Or

  • 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 Handbook for

  • 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

  • Oracle's binary. Octal. Decimal. Hexadecimal conversion function 2010-12-10

    We often encounter problems hex conversion, online search is only part of the conversion to decimal conversion of the other band, so I wrote it myself conversion functions available to everyone, a total of 12 4 hex conversion function, although some

  • jsp: useBean scope attribute the distinction between 2010-12-22

    JavaBean instance scope attribute specifies the domain stored in the scope of the object, its value can only be a page, request, session and application in one of four values, the default value is page. I can test the following code: date.jsp -------

  • 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

  • ORA-01157 ORA-01110 Alter system check datafiles 2011-03-07

    ORACLE RAC After the reported increase in data files: ORA-01157: can not identify / lock data file 21 - see DBWR trace file ORA-01110: data file 21: '/ dev/rlvsas_8g_data06' Execute 'alter system check datafiles' recovery; alter system check datafile

  • alter system flush shared_pool what has it done something 2011-03-24

    Sga our database is relatively small, the stored procedure are many and long, always appear oracle can not allocate the number of bytes of memory error. I was told you can use this statement to optimize the shared pool. I checked sql reference, that

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

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

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

processed in 0.491 (s). 12 q(s)