Shulou(Shulou.com)05/31 Report--

This article is about how to get the current system time in Oracle. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Get system current time

Type of date

select sysdate from dual;

char type

select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual; select to_char (sysdate, 'yy' ) from dual; --year select to_char(sysdate, 'MM' ) from dual; --month select to_char(sysdate, 'dd' ) from dual; --day select to_char(sysdate, 'Q') from dual; --quarter select to_char(sysdate, 'iw') from dual; --week--calendar type, 52 or 53 weeks per year

date action

current time minus 7 minutes 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; current time minus 7 months select sysdate,sysdate - interval '7' month from dual; select sysdate,sysdate - interval '7' year from dual; time interval multiplied by a number select sysdate,sysdate - 8*interval '7' hour from dual;

Common Timestamps

//Get January 1st of the year to_date(concat((select to_char(sysdate,'yy') from dual), '-01-01 00:00: 00'),' y-mm-dd HH24:mi: ss')//date format//Get January 1st of the month SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1))+1 FROM DUAL;/date format SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1))+1,'y-mm-dd HH24:mi: ss') FROM DUAL; //char format

trunc() function

trunc function processing numbers

This function does not round off the parts before or after the specified decimal, but truncates them all.

The syntax is as follows

TRUNC（number[,decimals]）

Of which:

number The number to be truncated

decimals Indicates the number of digits after the decimal point to reserve. Optional, omitting it truncates off all fractional parts.

1 select trunc(123.98)from dual;2 select trunc(123.123,2)from dual;3 select trunc(123.123,-1)from dual;

Note: The second parameter can be negative, which means that the part behind the specified number to the left of the decimal point is truncated, that is, all are recorded with 0. Similar to rounding, for example, if the parameter is 1, it is rounded to ten digits, if it is-1, it is rounded to ten digits, and so on; if the parameter set is negative, and the number of negative digits is greater than or equal to the number of bytes of the integer, it returns 0. TRUNC(89.985,-3)=0.

trunc function processing date

The trunc function returns a partially truncated date value in the specified meta element format.

The specific syntax format is as follows:

TRUNC（date,[fmt]）

Of which:

date is a required parameter and is a date value entered

The ignored fmt parameter is the date format used to truncate the date value entered in the specified element format. Ignore it and truncate it by the nearest date

Here is how this function is used:

1 trunc(sysdate,'y')--returns the first day of the current year.2 trunc(sysdate,' mm')--returns the first day of the current month.3 trunc(sysdate,'d')--returns the first day of the current week.4 select trunc(sysdate,' y') from dual;5 select trunc(sysdate,'MM') from dual;6 select trunc(sysdate,' D') from dual;/** *********** Date ***************/1.select trunc(sysdate) from dual --2013-01-06 Today's date is 2013 - 01-062.select trunc (sysdate, 'mm') from dual --2013-01-01 Returns the first day of the month.3.select trunc (sysdate,'yy') from dual --2013-01-01 Return to the first day of the year 4.select trunc (sysdate,'dd') from dual --2013-01-06 Returns the current date 5.select trunc (sysdate,'yy') from dual --2013-01-01 Returns the first day of the year 6.select trunc(sysdate,'d') from dual--2013-01-06 (Sunday) Returns the first day of the current week 7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 The current time is 17:35 8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC() function has no second precision/** ************ Number *******************/*TRUNC (number,num_digits) Number Number A number that needs to be rounded off. Num_digits Numbers used to specify rounding precision. The default value for Num_digits is 0. TRUNC() function intercepts without rounding */9.select trunc (123.458) from dual --12310.select trunc (123.458,0) from dual --12311.select trunc (123.458,1) from dual --123.412.select trunc (123.458,-1) from dual --12013.select trunc (123.458,-4) from dual --014.select trunc (123.458,4) from dual --123.45815.select trunc(123) from dual --12316.select trunc(123,1) from dual --12317.select trunc(123,-1) from dual --120

round function (rounded)

Description: Returns a value that is rounded to the specified number of decimal places.

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL

Parameters:

number : Number to be processed

decimal_places : rounding to a few decimal places (default is 0 )

1 Sample :2 select round(123.456, 0) from dual; select round(123.456, 1) from dual; return 123.54 select round(-123.456, 2) from dual; return-123.46

ceiling and floor functions

The ceiling and floor functions are sometimes useful when dealing with business data.

ceiling (n) takes the smallest integer greater than or equal to the value n;

floor(n) takes the largest integer less than or equal to the number n;

Thank you for reading! About "Oracle how to get the current system time and other operations" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.