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;