Talk about time functions

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.

1 2 3 4 5 6 7

October 6, 1234

Oracle/PLSQL: trunc function (with dates)

In Oracle/PLSQL,TruncFunction returns a date truncated to a specific unit of measure. The syntax forTruncFunction is: Trunc (date, [format]) DateIs the date to truncate. FormatIs the unit of measure to apply for truncating. IfFormatParameter is omitted,TruncFunction will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.   Below are the valid format parameters: Unit Valid format parameters Year Syyyy, yyyy, year, syear, YYY, YY, y ISO year Iyyy, Iy, I Quarter Q Month Month, Mon, mm, RM Week WW IW IW W W Day DDD, DD, J Start day of the week Day, Dy, d Hour HH, hh12, hh24 Minute Mi   For example: Trunc (to_date ('22-AUG-03 '), 'Year ') Wocould return '01-Jan-03' Trunc (to_date ('22-AUG-03 '), 'q ') Wocould return '01-JUL-03' Trunc (to_date ('22-AUG-03 '), 'month ') Wocould return '01-AUG-03' Trunc (to_date ('22-AUG-03 '), 'ddd ') Wocould return '22-AUG-03' Trunc (to_date ('22-AUG-03 '), 'day ') Wocould return '17-AUG-03'