Obtain the current system time

The date type of

select sysdate  from dual; 
Copy the code

The char type

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 
select  to_char(sysdate, 'yyyy') from dual; - in the select to_char (sysdate,'MM') from dual; - on the select to_char (sysdate,'dd') from dual; - select to_char (sysdate,'Q') from dual; Season - select to_char (sysdate,'iw') from dual; Weeks -- Calendar type, 52 or 53 weeks per yearCopy the code

Date of operation

Select sysdate, sysdate-interval'7'MINUTE from dual; Current time minus 7 hours select sysdate-interval'7'hour from dual; Current time minus 7 days select sysdate-interval'7'day from dual; Select sysdate, sysdate-interval'7'month from dual; Select sysdate, sysdate-interval'7'year from dual; Interval multiplied by a number select sysdate,sysdate -8 *interval'7' hour  from dual; 
Copy the code

Common timestamps

To_date (concat(select to_char(sysdate,'yyyy') from dual), '- 01-01 00:00:00'),'yyyy-MM-dd HH24:mi:ss'SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 FROM DUAL; SELECT LAST_DAY(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) FROM DUAL; SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1,'yyyy-mm-dd HH24:mi:ss') FROM DUAL; / / char formatCopy the code

Trunc () function

The trunc function processes numbers

This function does not round off the portion before or after the specified decimal. The syntax is as follows: TRUNC (number[,decimals]) : Number Decimals indicates the number of digits following the decimal point. Optional. Ignoring it truncates all decimal parts.

1 select trunc (123.98) from dual; 2 the select trunc (123.123, 2) from dual; 3 the select trunc (123.123, 1) from dual;Copy the code

Note: The second argument can be negative and represents the truncation of the specified number to the left of the decimal point, i.e., all zeros. It’s similar to rounding, for example, to the tenth place if it’s 1, to the tens place if it’s -1, and so on; If the parameter is set to a negative number and the number of bits of the negative number is greater than or equal to the number of bytes of the integer, 0 is returned. Such as: TRUNC (89.985, 3) = 0.

The trunc function returns a truncated date value in the specified metaelement format. The syntax is as follows: TRUNC (date,[FMT]) Where: Date is a required parameter and is a date value to be entered. FMT is an ignorable parameter and is a date format. It is used to truncate the entered date value with the specified element format. If you omit it, you will truncate it by the most recent date.

1 trunc(sysdate,'yyyy') -- returns the first day of the current year.'mm'Trunc (sysdate, trunc) -- returns the first day of the month'd'4 Select trunc(sysdate, trunc,'YYYY')from dual;
5 select trunc(sysdate,'MM')from dual;
6 select trunc(sysdate,'D')from dual;
Copy the code
/ * * * * * * * * * * * * * * date * * * * * * * * * * * * * * * * * * * * / 1. Select trunc (sysdate) from dual today's date for the 2013-01-06-2013-01-06 (2) to select trunc(sysdate,'mm') from dual --2013-01-01 Returns the first day of the month.'yy'Select trunc(sysdate, 1) from dual --2013-01-01'dd') from dual --2013-01-06 select trunc(sysdate,'yyyy'Select trunc(sysdate, 2013-01-01) from dual'd') from dual --2013-01-06 (Sunday)'hh') from dual --2013-01-06 17:00:00'mi') from dual - 2013-01-06 17:35:00 TRUNC () function is no accurate / * * * * * * * * * * * * * * * number * * * * * * * * * * * * * * * * * * * * / / * TRUNC (number, num_digits) Number Indicates the Number to be rounded. Num_digits specifies the number for the rounding accuracy. The default value for Num_digits is 0. TRUNC() */ 9. Select TRUNC(123.458) from dual --123 10. Select TRUNC(123.458,0) from dual --123 11 Select Trunc (123.458,-1) from dual --0 select Trunc (123.458,-4) from dual --0 Select Trunc (123.458,4) from dual --123.458 15. Select Trunc (123) from dual --123 16 --123 17.select trunc(123,-1) from dual --120Copy the code

Round function (rounded)

Description: Returns a value that is the result of rounding the specified number of decimal elements.

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL
Copy the code

Parameters:

Number: number of places to be processed decimal_places

1 Sample: 2 select round(123.456, 0) from dual; Select round(123.456, 1) from dual; 4 Select round(-123.456, 2) from dual; Back to 123.46Copy the code

Ceil and floor functions

The ceil and floor functions are sometimes useful for business data. Ceil (n) takes the smallest integer greater than or equal to the number n; Floor (n) takes the largest integer less than or equal to the value n;