Time Application in Oracle

Source: Internet
Author: User
Tags date1 time zones

1. conversion functions
The biggest relationship with the date operation is the two conversion functions: to_date (), to_char ()
To_date () converts the character type to the date type in a certain format:
Specific usage: to_date ('1970-11-27 ', 'yyyy-mm-dd'). The former is a string, and the latter is a conversion date format. Note that the first and second data must be in the correct format.
For example, to_date ('2017-11-27 13:34:43 ', 'yyyy-mm-dd hh24: MI: ss') will get the specific time

Multiple date formats:

Yyyy: Year in four bits
YYY, YY, Y: the last three digits, two digits, or one digit of the year. The default value is the current century.
MM: 01 ~ 12 month number
Month: The month, which contains nine characters. Fill the month with spaces on the right.
Mon: the abbreviation of A Three-character month
WW: week of the year
D: The day of the week
DD: The day of the month.
DDD: The day of the year
Day: The full name of the day, expressed in nine characters, and filled with spaces on the right
HH, hh12: the hour of the day, 12 hexadecimal notation
Hh24: The hour in a day. The value ranges from 00 ~ 23
Mi: minute in an hour
SS: seconds in one minute
Ssss: the number of seconds since midnight

To_char (): Convert the date to a certain format to the character type
SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') time from dual;

Time
-------------------
2004-10-08 15:22:58

Converts the current time to the character type in yyyy-mm-dd hh24: MI: SS format.

Processing date in Oracle

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

The time range in the 24-hour format is: 0:00:00-23:59:59 ....
The time range in the 12-hour format is: 1:00:00-12:59:59 ....

[ZT] Date and ?? Letter ???
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.
The day of the week.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual;
Monday
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ date_language = American') from dual;
Monday
Set the date language
Alter session set nls_date_language = 'American ';
You can also
To_date ('1970-08-26 ', 'yyyy-mm-dd', 'nls _ date_language = American ')

4.
Number of days in a two-day period
Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

5. Use a time of null
Select ID, active_date from Table1
Union
Select 1, to_date (null) from dual;

Note that to_date (null) is used)

6.
A_date between to_date ('20140901', 'yyyymmdd') and to_date ('20140901', 'yyyymmdd ')
Therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 1.
Therefore, when the time needs to be accurate, to_char is still necessary.
7. Date Format conflict
The input format depends on the type of the Oracle character set you installed, for example, us7ascii. The date format is '01-Jan-01'
Alter system set nls_date_language = American
Alter session set nls_date_language = American
Or write it in to_date.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ date_language = American') from dual;
Note that I just mentioned nls_date_language. Of course there are many more,
Available
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 ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'd ')
Not
In ('1', '7 ')

Find the number of days between and except Monday and seven
Call dbms_utility.get_time to subtract the result (1/100 seconds instead of 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 time of the first record is the same as that of the last record.
You can create a function to solve 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.
Hours

Select extract (hour from timestamp '2017-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 ')
-------------------------------------------
19:35:21 19

Obtain the year, month, and day.
13.
Processing of year, month, and day
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.
How to deal with the uncertainty of the number of days in a month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

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

How to deal with a leap year
To_char (last_day (to_date ('02 '|: Year, 'mmyyyy'), 'dd ')
If it is 28, it is not a leap year.

17.
Difference between yyyy and RRRR
'Yyyy99 to_c
-----------
Yyyy 99 0099
Rrrr 99 1999
Yyyy 01 0001
Rrrr 01 2001

18. Processing in different time zones
Select to_char (new_time (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: MI: ss'), sysdate
From dual;

19.
One interval in five seconds
Select to_date (floor (to_char (sysdate, 'ssss')/300) * 300, 'ssss'), to_char (sysdate, 'ssss ')
From dual

2002-11-1 9:55:00 35786
Sssss indicates five seconds

20.
The day of the year
Select to_char (sysdate, 'ddd '), sysdate from dual
310 10:03:51

21. Computing hour, minute, second, millisecond
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)
From dual
)

 

Select * From tabname
Order by deCODE (mode, 'fifo ', 1,-1) * to_char (RQ, 'yyyymmddhh24miss ');

//
Floor (date2-date1)/365) as year
Floor (date2-date1, 365)/30) as month
MoD (mod (date2-date1, 365), 30) as daily.
23. next_day Function
Next_day (sysdate, 6) is the next Friday from the current start. The following number is counted from Sunday.
1 2 3 4 5 6 7
October 6, 1234

Oracle has many date functions.

There are many date functions in Oracle, such:
1. add_months () is used to increase or decrease the number of months from a date value.
Date_value: = add_months (date_value, number_of_months)
Example:
SQL> select add_months (sysdate, 12) "next year" from dual;

Next year
----------
April 11-04

SQL> select add_months (sysdate, 112) "last year" from dual;

Last year
----------
13-3-13

SQL>

2. current_date () returns the current date in the current time zone.
Date_value: = current_date
SQL> column sessiontimezone for A15
SQL> select sessiontimezone, current_date from dual;

Sessiontimezone current_da
-------------------------
+-11-03

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

The session has been changed.

SQL> select sessiontimezone, current_timestamp from dual;

Sessiontimezone current_timestamp
---------------------------------------------------
--03 04.59.13.668000 pm-11:
00

SQL>

3. current_timestamp () returns the current date in the current time zone as the timestamp with Time Zone Data Type
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
---------------------------------------------------
+-03 11.56.28.160000 AM + 08:
00

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

The session has been changed.

SQL> select sessiontimezone, current_timestamp from dual;

Sessiontimezone current_timestamp
---------------------------------------------------
--03 04.58.00.243000 pm-11:
00

SQL>

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

Dbtime
------
-07:00

SQL>

5. Extract () identifies the field value of the date or interval value
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 of the last day of the month that contains the date parameter.
Date_value: = last_day (date_value)
SQL> select last_day (date '2017-02-01 ') "Leap yr? "From dual;

Leap yr?
----------
29-2-00

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

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

SQL>

7. localtimestamp () returns the date and time in the session.
Timestamp_value: = localtimestamp
SQL> column localtimestamp format A28
SQL> select localtimestamp from dual;

Localtimestamp
----------------------------
13-11-03 12.09.15.433000
Afternoon

SQL> select localtimestamp, current_timestamp from dual;

Localtimestamp current_timestamp
----------------------------------------------------------------
13-11-03 12.09.31.006000 13-11-03 12.09.31.006000 PM + 08:
00 pm

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

The session has been changed.

SQL> select localtimestamp, to_char (sysdate, 'dd-MM-YYYY hh: MI: SS am') "sysdate" from dual;

Localtimestamp sysdate
----------------------------------------------------
12-11-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon

SQL>

8. months_between () determines the number of months between two dates.
Number_value: = months_between (date_value, date_value)
SQL> select months_between (sysdate, date '2017-05-18 ') from dual;

Months_between (sysdate, date '2017-05-18 ')
----------------------------------------
389.855143

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

Months_between (sysdate, date '2017-01-01 ')
----------------------------------------
34.4035409

SQL>

9. Given a date value, next_day () returns the date value that appears for the first time on the day indicated by the second parameter (the name string of the corresponding day should be returned)

? Zhou Xiang? Date letter?

1. query the first day of a week
Select trunc (decode (WW, 53, to_date (yy | '000000', 'yyyyddmm'), to_date (yy | '-' | to_char (WW * 7 ), 'yyyy-DDD '), 'D') last_day
From (select substr ('1970-32', 1, 4) YY, to_number (substr ('1970-32', 6) ww
From dual)

Select trunc (to_date (substr ('1970-01', 2003) | to_char (to_number (substr ('1970-01', 6) * 7 ), 'yyyy-DDD '), 'D')-6 first_day from dual

Select min (v_date) from
(Select (to_date ('20170101', 'yyyymmm ') + rownum) v_date
From all_tables
Where rownum <370)
Where to_char (v_date, 'yyyy-iw') = '2017-49'

2. query the last day of a week
Select trunc (decode (WW, 53, to_date (yy | '000000', 'yyyyddmm'), to_date (yy | '-' | to_char (WW * 7 ), 'yyyy-DDD '), 'D')-6 first_day
From (select substr ('2014-33 ', 1, 4) YY, to_number (substr ('2014-33', 6) ww
From dual)

Select trunc (to_date (substr ('1970-01', 2003) | to_char (to_number (substr ('1970-01', 6) * 7 ), 'yyyy-DDD '), 'D') last_day from dual

Select max (v_date) from
(Select (to_date ('20170101', 'yyyymmm ') + rownum) v_date
From all_tables
Where rownum <370)
Where to_char (v_date, 'yyyy-iw') = '2017-33'

3. query the date of a week
Select min_date, to_char (min_date, 'day') day from
(Select to_date (substr ('1970-33', 2004) | '001' + rownum-1, 'yyyyddd ') min_date
From all_tables
Where rownum <= decode (mod (to_number (substr ('1970-33', 2004), 4), 0,366,365)
Union

Select to_date (substr ('2014-33 ', 2004)-1 |
Decode (mod (to_number (substr ('1970-33', 2004)-0,359,358),) + rownum, 'yyyyddd ') min_date
From all_tables
Where rownum <= 7
Union

Select to_date (substr ('1970-33', 2004) + 1 | '001' + rownum-1, 'yyyyddd ') min_date
From all_tables
Where rownum <= 7
)
Where to_char (min_date, 'yyyy-iw') = '2017-33'
 
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.