Oracle date type and processing method explanation, oracle Date Processing Method

Oracle date type and processing method explanation, oracle Date Processing Method
Date Processing get current date-sysdate ()

Sysdate () is accurate to seconds.

Add a specific month to the date-add_months ()

select add_months(to_date('2010-2-27','YYYY-MM-DD'),1) new_date from dual

Result: 2010-3-27

If the period of January 1, February is only 28 days, the last day of January 1, March is returned after a month is added.

select add_months(to_date('2010-2-28','YYYY-MM-DD'),1) new_date from dual

Result: 2010-3-31

If the date after the month is added is an invalid date, the last day of the month is returned.

select add_months(to_date('2010-1-30','YYYY-MM-DD'),1) new_date from dual

Result:

Returns the last day of the month of the specified date-last_day ()

select last_day(to_date('2010-2-28','YYYY-MM-DD')) new_date from dual

Result:

Returns the number of months with the difference of two dates-months_between ()

Months_between () returns the number of months after the subtraction of two dates. The returned value is not necessarily an integer.

select months_between(to_date('2010-4-4','YYYY-MM-DD'),to_date('2010-2-6','YYYY-MM-DD')) as new_date from dual

Result: 1.93548 ....

If the first parameter is smaller than the second parameter, the return value is a negative number.

Returns the date of the week after the specified date-next_day ()

select next_day(to_date('2010-4-4','yyyy-mm-dd'),2) new_date from dual

Result:

The second parameter indicates the day of the week. in Oracle, 1 indicates Sunday, 2 indicates Monday...

Trunc ()

Trunc (date, truncation format) select trunc (sysdate, 'dd') new_date from dual

Result:

select trunc(sysdate,'MM') new_date from dual

Result: 2010-4-1

Trunc (sysdate, 'mm') truncates the current date to the month. The result is 2010-4-1, that is, the first day of the current month.

select trunc(sysdate,'MI') new_date from dual

Result: 15:44:00

Trunc (sysdate, 'mi') truncates the current date to minutes, ignoring the part after the minute. The actual truncation date is to set some of the information to 0.

Returns the current date of the current session time zone-current_date ()

select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh:mi:ss') result from dual

Result: + 4:25:26

Sessiontimezone returns the current time zone, and current_date returns the current date.

Returns the timestamp of the current session time zone-current_timestamp ()

select sessiontimezone,current_timestamp from dual

Result: + Pm + pm

Returns a field of the date-extract ()

Select extract (domain name, from date) select extract (month from sysdate) new_month from dual

Result; 4

Note: The hour, minute, second, and other information cannot be decomposed in the date type. To obtain information such as hours, you must use the date and time type as the data source.

select extract(hour from systimestamp) new_month from dual

Result: 14

Note: The Hour Information is the standard time of the zero time zone.

You can create a function to obtain all the information of a date.

create or replace function get_field(p_date date,p_format varchar2)return varchar2isval varchar2(10);tmp_date varchar2(20);begintmp_date:=to_char(p_date,'yyyy-mm-dd hh24:mi:ss');if lower(p_format)='year' thenval:=substr(tmp_date,1,4);end if;if  lower(p_format)='month' thenval:=substr(tmp_date,6,2);end if;if  lower(p_format)='day' thenval:=substr(tmp_date,9,2);end if;if  lower(p_format)='hour' thenval:=substr(tmp_date,12,2);end if;if  lower(p_format)='minute' thenval:=substr(tmp_date,15,2);end if;if  lower(p_format)='second' thenval:=substr(tmp_date,18,2);end if;return val;end;

Convert a date to a string-to_char ()

select to_char(sysdate,'yyyy-mm-dd') new_date from dual

Result: