Page 1/2 of Oracle date function set (centralized Version)

In the development of Oracle databases, due to time issues, the weekly chapter is too large, so the date functions of Oracle Data are specially added to favorites. It is for another day's query.
Add_months (d, n) date D plus N months
What month does last_day (d) contain D? The date of the last day
New_time (D, a, B)? Partition date and ?? D In B? Partition date and ??
Next_day (D, day) is more than the date D ?, Date of the week specified by day
Current sysdate system? Date and ??
Greatest (D1, D2,... DN )? The last date in the output date list
Least (D1, K2,... DN )? The earliest date in the output date list
To_char (d [, FMT]) date D is in the format specified by FMT ?? String
Is the to_date (ST [, FMT]) string ST in the format specified by FMT? Into date ?, If FMT is ignored, the St must use the default format.
Round (d [, FMT]) date D rounds to the latest date in the format specified by FMT
Trunc (d [, FMT]) date D is truncated to the latest date in the format specified by FMT
Convert to_date string type to date type
Character in the corresponding position in the string, must meet the time range limit

Query the Oracle Date Format
----------------------------------

Select * From nls_database_parameters;

The following table shows the date format in the table nls_date_format.
Parameter Value
----------------------------------------------------------------------
Nls_language American
Nls_territory America
Nls_currency $
Nls_iso_currency America
Nls_numeric_characters .,
Nls_characterset zhs16gbk
Nls_calendar Gregorian
Nls_date_format DD-MON-RR
Nls_date_language American
Nls_sort binary
Nls_time_format HH. Mi. ssxff AM
Nls_timestamp_format DD-MON-RR HH. Mi. ssxff AM
Nls_time_tz_format HH. Mi. ssxff am tzh: tzm
Nls_timestamp_tz_format DD-MON-RR hh. mi. ssxff am tzh: tzm
Nls_dual_currency $
Nls_comp binary
Nls_nchar_characterset zhs16gbk
Nls_rdbms_version 8.1.7.0.0

Or query the V $ nls_parameters table,
Select * from V $ nls_parameters;
There are similar results

SQL> select to_date ('2017-11-12 12-07-32 ', 'yyyy-mm-dd hh24-mi-ss') value from dual;
Value
-------------------
2004.11.12 12:07:32

SQL> select to_date ('20140901') value from dual;
Value
-------------------
2004.10.15 00:00:00

SQL> select to_date ('20140901') value from dual;
Error is located in row 1st:
ORA-01861: Text and format strings do not match

Sysdate current date and time

SQL> select sysdate value from dual;
Value
-------------------
2003.11.23 17:09:01

Last_day

SQL> select last_day (sysdate) value from dual;
Value
-------------------
2003.11.30 17:08:17

Add_months (d, n) date D is pushed n Months Later

SQL> select add_months (sysdate, 2) value from dual;
Value
-------------------
2005.01.23 17:10:21

What is the date of the specified day (the day of the week) in the first week after the next_day (D, day) Date

SQL> select next_day (sysdate, 1) value from dual;
Value
-------------------
2004.11.28 17:38:55

[Oracle/PLSQL] Full Oracle date processing version

Date Processing Full Edition
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 ....
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.

A simple sentence.
The Oracle date is treated as a special number in days.
Date + number = date, date-date = number, date-number = Date

The formatted display of mestamp data is the same as that of date data. Note that the to_char function supports date and timestamp, but trunc does not support the timestamp data type. This clearly shows that the use of Timestamp data type is more accurate than the date data type when the difference between the two time is extremely important.

If you want to display the fractional seconds of Timestamp, refer to the following:

1 select to_char (time1, 'Mm/DD/YYYY hh24: MI: SS: ff3') "date" from date_table

Date

-----------------------

06/20/2003 16: 55: 14: 000

06/26/2003 11: 16: 36: 000

In the preceding example, only three digits after the decimal point are allowed.

It is easier to calculate the data difference between timestamp than the old date data type. If you subtract it, you can see what will happen. The result is easier to understand, 17 days, 18 hours, 27 minutes, and 43 seconds in the first line.

1 select time1,

2 time2,

3 substr (time2-time1), instr (time2-time1), '') +) seconds,

4 substr (time2-time1), instr (time2-time1), '') +) minutes,

5 substr (time2-time1), instr (time2-time1), '') +) hours,

6 trunc (to_number (substr (time2-time1), 1, instr (time2-time1, '') days,

7 trunc (to_number (substr (time2-time1), 1, instr (time2-time1, '')/7) weeks

8 * From date_table

Time1 time2 seconds minutes hours days weeks

-------------------------------------------------------------------------------

06/20/2003: 16: 55: 14: 000000 07/08/2003: 11: 22: 57: 000000 43 27 18 17 2

06/26/2003: 11: 16: 36: 000000 07/08/2003: 11: 22: 57: 000000 21 06 00 12 1

This means that you no longer need to worry about how many seconds a day is in troublesome computing. Therefore, getting the number of days, months, days, hours, minutes, And seconds is the use of the substr function to extract numbers.

System Date and Time

To obtain the system time, the Data Type of date is returned. You can use the sysdate function.

SQL> select sysdate from dual;

To obtain the system time, the data type is returned as timestamp. You can use the aggregate impstamp function.

SQL> select distinct imestamp from dual;

You can set the initialization parameter fixed_date to specify the sysdate function to return a fixed value. This is used when the test date and time are sensitive. Code . Note that this parameter is invalid for the systimestamp function.

SQL> alter system set fixed_date = '2017-01-01-10:00:00 ';

System altered.

SQL> select sysdate from dual;

Sysdate

---------

01-Jan-03

SQL> select distinct imestamp from dual;

Systimestamp

---------------------------------------------------------

09-jul-03 11.05.02.519000 am-06:00

When the date and timestamp types are used, the selection is clear. You can handle the date and timestamp types at will. When you try to convert to a more powerful timestamp, You need to note that they both have similarities and differences, which is enough to cause damage. The two have their own advantages in conciseness and interval size. Please choose a proper one.

Datediff (month, waterpay. copydate, getdate () = 1)

Datediff (day, waterpay. copydate, getdate () = 1)
The meaning of these two sentences is: Calculate the record that returns the value of a date field (waterpay. copydate) in the database minus 1 in the form of month or day
How can I rewrite these two functions in SQL Server in Oracle?

Number of questions: 20. replies: 2
Top

Hevin (nothing is impossible) on the first floor replied to the first sentence with a score of 0 at 18:12:10:
Months_between (to_date (to_char (sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd '),
To_date (to_char (waterpay. copydate, 'yyyy-mm-dd'), 'yyyy-mm-dd ')
) = 1
Top

Hevin on the second floor (nothing is impossible) replied to the second sentence with a score of 20 at 18:18:06:
To_char (sysdate-1, 'yyyy-mm-dd') = to_char (waterpay. copydate, 'yyyy-mm-dd ')