Common Oracle functions-date functions, oracle date functions

Common Oracle functions-date functions, oracle date functions

In the development and use of oracle, a variety of date functions are often used. The online date functions are searched for in batch, and there are many minor problems to use. I will summarize and record them myself, it can also reduce the number of detours for those who are interested.

-- Common date functions

1. Sysdate current date and time

Select sysdate from dual;

2. Add_months (d, n) the current date d is pushed n Months Later

Used to increase or decrease the number of months from a date value.

Two months after the current date:

Select add_months (sysdate, 2) from dual;

Three months before the current date:

Select add_months (sysdate,-3) from dual;

 

3. dbtimezone () returns the time zone

Select dbtimezone from dual;

 

4. round [round to the nearest date] (day: round to the nearest Sunday)

Select sysdate S1,

Round (sysdate) S2,

Round (sysdate, 'Year') year,

Round (sysdate, 'month') month,

Round (sysdate, 'day') day fromdual

 

5. next_day (date, weekday) specifies the next date of the date.

Syntax: next_day (date, weekday)

Select Next_day (to_date (20130107, 'yyyymmdd'), 2) from dual;

This function is very powerful, but it should be noted that Sunday is 1, Monday is 2, and so on.

 

6. trunc [truncates to the closest date, in days] and returns the date type.

Select sysdate S1,

Trunc (sysdate) S2, -- returns the current date, no time, minute, second

Trunc (sysdate, 'Year') year, -- returns January 1, January 1 of the current YEAR.

Trunc (sysdate, 'month') month, -- returns the 1 day of the current MONTH, no hour, minute, second

Trunc (sysdate, 'day') day -- returns Sunday of the current week, with no hour, minute, second

From dual

7. last_day the last day of the month

Select last_day (Trunc (SYSDATE, 'month') from dual

Returns the last day;

If selectlast_day (SYSDATE) from dual is used directly, not only the date is returned, but also the current hour, minute, and second are returned;

 

8. to_date, to_char date, and character conversion function usage

Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') as nowTime from dual; -- convert date to string

Select to_char (sysdate, 'yyyy') as nowYear from dual; -- get the year of the time

Select to_char (sysdate, 'q') asnowSecond from dual; -- Obtain the quarter of the time

Select to_char (sysdate, 'mm') asnowMonth from dual; -- get the month of the time

Select to_char (sysdate, 'dd') asnowDay from dual;-get the date of the time

Select to_char (sysdate, 'hh24') as nowHour from dual; -- get the time

Select to_char (sysdate, 'mi') asnowMinute from dual; -- get the time score

Select to_char (sysdate, 'ss') asnowSecond from dual; -- get the second of the time

Convert string to date type

Select to_date ('2017-05-07 13:23:44 ', 'yyyy-mm-dd hh24: mi: ss') from dual;

 

9. The month difference between months_between and months_between

Select months_between (to_date (201302, 'yyymmm'), to_date (201212, 'yyymmm') as ans from dual;

 

10. localtimestamp () returns the date and time in the session.

Select localtimestamp from dual;

Common date data format (This section is excerpted)

Y or YY or YYY, the last digit of the year, two or three Select to_char (sysdate, 'yyy') from dual; 002 indicates 2002

SYEAR or year syear Add a minus sign before the YEAR of BC Select to_char (sysdate, 'syear ') from dual;-1112 indicates 111 BC 2 years

Q quarter, 1 ~ Select to_char (sysdate, 'q') from dual for the First Quarter of March; 2 indicates the Second Quarter

MM Month number Select to_char (sysdate, 'mm') from dual; 12 indicates December

In the month of RM, Rome indicates Selectto_char (sysdate, 'rm ') from dual; IV indicates April

Month: The Month name expressed by 9 characters. Select to_char (sysdate, 'month') from dual; May is expressed by 6 spaces on January 1, May.

Select to_char (sysdate, 'ww ') from dual; 24 indicates 24th weeks

W the week of this month Select to_char (sysdate, 'w') from dual; 1st is week 1

The number of DDD in the current year. The value of January 1 is 032 Select to_char (sysdate, 'ddd ') from dual; 363, the value of 2002 is 363rd days.

DD: Select to_char (sysdate, 'dd') from dual; 04, January 1, October 4, 4th

Select to_char (sysdate, 'D') from dual; 5 Monday, January 1, March 14, 2002

The day of the DY week is called Selectto_char (sysdate, 'dy ') from dual; SUN is Sunday on March 24, 2002

HH or HH12 12 hexadecimal hours Selectto_char (sysdate, 'hh ') from dual; 02 PM + Pm divided into 02

HH24 24-hour Select to_char (sysdate, 'hh24') from dual; 14 02:08 P.M. is 14

MI minutes (0 ~ 59) Select to_char (sysdate, 'mi') from dual; 17 04:17 P.M.

SS seconds (0 ~ 59) Select to_char (sysdate, 'ss') from dual; 22 3 minutes 22 seconds

Note: Do not use MM format for minutes (MI should be used for minutes ). MM is used for the month format. It can work in minutes, but the result is incorrect.

 

-- Specific usage of date functions

 

1. Get the last day of the previous month:

Select to_char (add_months (last_day (sysdate),-1), 'yyyy-MM-dd') LastDay fromdual;

 

2. today of the previous month (same date)

Select to_char (add_months (sysdate,-1), 'yyyy-MM-dd') PreToday from dual;

 

3. The first day of last month

Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyy-MM-dd') First_Day_Last_Month from dual;

SELECT Trunc (SYSDATE, 'month')-1, 'month') First_Day_Last_Month FROM dual;

 

4. Obtain the day of the week corresponding to the date

Select to_char (to_date ('1970-04-07 ', 'yyyy-mm-dd'), 'day') from dual;

5. Number of days from the current date to a certain date 

Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

 

6. Find the number of days this year.

Selectadd_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual

This year is 366 days or 365 days.

 

7. Simply find a leap year

If the number of days in March is 28, it is not a leap year.

Select decode (to_char (last_day (to_date ('201312', 'yyyml'), 'dd'), '28', 'not a leap year', 'leap year') from dual

8. The current date is the day of the year

Selectto_char (sysdate, 'ddd '), sysdate from dual;

9. Search for the first and last moments of the previous month, the first and last moments of the current month.

SELECT Trunc (SYSDATE, 'month')-1, 'month') First_Day_Last_Month,

Trunc (SYSDATE, 'month')-1/86400 Last_Day_Last_Month,

Trunc (SYSDATE, 'month') First_Day_Cur_Month,

LAST_DAY (Trunc (SYSDATE, 'month') + 1-1/86400 Last_Day_Cur_Month

FROM dual;