Date and time functions in ORACLE summary (reproduced)

sponsored links
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.
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Date and time functions in ORACLE summary (reproduced)

  • Professional IT management talent required ability Ten

    Technical article ] [IT168 As an IT technical staff, that no one person is willing to Forever in the bottom of the preparation procedure or to do simple maintenance. After a period of technology and the accumulation of experience, a lot of people are ...

  • The company first entered the company I would like to Optimize SQL project

    I was a time not long into the company of people, but also a person to be graduated, but immediately entered the project team, involved in the development. The project is to make a China Telecom Operation System, is a big system. My so-called develop ...

  • JS in the Date type conversion

    http://blog.csdn.net/yzy0612/archive/2007/08/07/1730732.aspx

  • js page Jump implementation of a number of ways

    The first is: <script language="javascript" type="text/javascript"> window.location.href = "login.jsp? backurl =" + window.location.href; </ script> The second: <script language="javascript"> alert

  • RoR explained

    ROR is Ruby on Rails. Ruby is a well-known has been very good dynamic language It's dynamic language. Simple and easy. Dynamic languages are interpreted, but the performance may make a discount, but not absolute, because the application is complex, th

  • Hibernate primary key strategy-sequence

    Today, the use of hibernate in the company encountered a troublesome problem, the use of hibernate when the primary key generation strategy set sequence, but always reported in the implementation could not get next sequence value of the error, then o ...

  • Some interview questions java

    The first is the company give you a chance to meet, it is necessary to know to meet from time to equal the interview, and have a lot of companies to see you at the first time will give you a ready point of doing something trivial, these questions, althoug

  • Oracle instant clent for ruby / rails on cygwin

    Environment: XP: oracle full client, ruby, rails, gem cygwin: ruby rails, gem (the version with the XP version) Needs: for cygwin is installed under the rails platform support oci Steps: <1> download oracle instant client (10.2.0.3 Instant Client Pa

  • spring + hibernate + oracle9i to read and write CLOB

    Database-driven update classes12-9i.jar Hibernate modify the configuration of the following code <bean/> <bean Lazy-init="true"> <property name="nativeJdbcExtractor"> <ref local="nativejdbcExtractor"/>

  • Hibernate connection pool configuration

    Hibernate connection pool configuration <! - Jdbc -> <property name="connection.driver_class"> oracle.jdbc.driver.OracleDriver </ property> <property name="connection.url"> jdbc: oracle: thin: @ 10.203.14.132:15

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries