Shulou(Shulou.com)06/03 Report--

This article mainly explains the "ORACLE date-time function Daquan", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "ORACLE date-time function Daquan"!

TO_DATE format (take time: 2007-11-02 13:45:25 as an example)

Year:

Yy two digits two-digit year display value: 07

Yyy three digits 3-digit year display value: 007

Yyyy four digits four-digit year display value: 2007

Month:

Mm number two-digit month display value: 11

The mon abbreviated character set represents the display value: November, if the English version, display nov

The month spelled out character set represents the display value: November, if the English version, display november

Day:

The day of the month in dd number displays the value: 02

The day of the year in which ddd number displays the value: 02

Dy abbreviated the day of the week abbreviated display value: Friday, if the English version, display fri

Day spelled out full write display value on the day of the week: Friday, if the English version, display friday

Ddspth spelled out, ordinal twelfth

Hour:

Hh two digits 12-hour display value: 01

Hh34 two digits 24-hour display value: 13

Minute:

Mi two digits 60 binary display value: 45

Second:

Ss two digits 60 binary display value: 25

Other

Q digit quarterly display value: 4

WW digit shows a value of 44 in what week of the year

W digit shows the value of the week of the month: 1

Under the 24-hour format, the time range is 0:00:00-23 59.

The time range in the 12-hour format is: 1:00:00-12:59:59.

1. Date and character conversion function usage (to_date,to_char)

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') as nowTime from dual; / / date converted to a string

Year of select to_char (sysdate,'yyyy') as nowYear from dual; / / acquisition time

Select to_char (sysdate,'mm') as nowMonth from dual; / / month of acquisition time

Select to_char (sysdate,'dd') as nowDay from dual; / / the day on which the time is obtained

Select to_char (sysdate,'hh34') as nowHour from dual; / / when getting the time

Select to_char (sysdate,'mi') as nowMinute from dual; / / minutes of acquisition time

Select to_char (sysdate,'ss') as nowSecond from dual; / / get the seconds of the time

Select to_date ('2004-05-07 13 from dual// 23 Swiss 44 mm hh34:mi:ss') from dual//

two。

Select to_char (to_date (222 Jsp' J') from dual

Show Two Hundred Twenty-Two

3. Ask what day it is one day

Select to_char (to_date ('2002-08-26) from dual

Monday

Select to_char (to_date ('2002-08-26) from dual

Monday

Set date language

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'

You can do that, too.

TO_DATE ('2002-08-26,' YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

4. The number of days between two days

Select floor (sysdate-to_date ('20020405)) from dual

5. Time is the use of null

Select id, active_date from table1

UNION

Select 1, TO_DATE (null) from dual

Pay attention to using TO_DATE (null)

6. Monthly difference

A_date between to_date ('200112019) and to_date (' 20011231)

Then after 12:00 on December 31st and before 12:00 on December 1st are not included in this range.

So, when the time needs to be precise, I think to_char is still necessary.

7. Date format conflict problem

The format you enter depends on the type of ORACLE character set you install, for example: US7ASCII, the type of date format is:'01'

Alter system set NLS_DATE_LANGUAGE = American

Alter session set NLS_DATE_LANGUAGE = American

Or write in to_date

Select to_char (to_date ('2002-08-26) from dual

Notice that I'm just raising NLS_DATE_LANGUAGE, and of course there's a lot more.

Viewable

Select * from nls_session_parameters

Select * from V$NLS_PARAMETERS

8.

Select count (*)

From (select rownum-1 rnum

From all_objects

Where rownum select sysdate, to_char (sysdate,'hh') from dual

SYSDATE TO_CHAR (SYSDATE,'HH')

--

2003-10-13 19:35:21 07

SQL > select sysdate, to_char (sysdate,'hh34') from dual

SYSDATE TO_CHAR (SYSDATE,'HH24')

2003-10-13 19:35:21 19

13. The treatment of the year, month and day

Select older_date

Newer_date

Years

Months

Abs (

Trunc (

Newer_date-

Add_months (older_date,years*12+months)

)

) days

From (select

Trunc (months_between (newer_date, older_date) / 12) YEARS

Mod (trunc (months_between (newer_date, older_date)), 12) MONTHS

Newer_date

Older_date

From (

Select hiredate older_date, add_months (hiredate,rownum) + rownum newer_date

From emp

)

)

14. The way to deal with the variable number of days of the month

Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

16. Find out the number of days this year

Select add_months (trunc (sysdate,'year'), 12)-trunc (sysdate,'year') from dual

The treatment of leap years

To_char (last_day (to_date ('02' | |: year,'mmyyyy')),' dd')

If it's 28, it's not a leap year.

The difference between 17.yyyy and rrrr

'YYYY99 TO_C

-

Yyyy 99 0099

Rrrr 99 1999

Yyyy 01 0001

Rrrr 01 2001

18. Treatment of different time zones

Select to_char (NEW_TIME (sysdate, 'GMT','EST'),' dd/mm/yyyy hh:mi:ss'), sysdate

From dual

19.5 seconds at an interval

Select TO_DATE (FLOOR (TO_CHAR (sysdate,'SSSSS') / 300) * 300), TO_CHAR (sysdate,'SSSSS')

From dual

2002-11-1 9:55:00 35786

SSSSS represents 5-digit seconds

20. The day of the year

Select TO_CHAR (SYSDATE,'DDD'), sysdate from dual

310 2002-11-6 10:03:51

21. Calculate hours, minutes, seconds, milliseconds

Select

Days

A

TRUNC (Atom 24) Hours

TRUNC (Atom 24060-60*TRUNC (Atom 24)) Minutes

TRUNC (Av2406060-60*TRUNC (A24060)) Seconds

TRUNC (A24 / 60 / 60 / 100-100*TRUNC) mSeconds

From

(

Select

Trunc (sysdate) Days

Sysdate-trunc (sysdate) A

From dual

)

Select * from tabname

Order by decode (mode,'FIFO',1,-1) * to_char (rq,'yyyymmddhh34miss')

/ /

Floor ((date2-date1) / 365) as a year

Floor ((date2-date1, 365) / 30) as month

D (mod (date2-date1, 365,30) as a day.

The 23.next_day function returns the date of next week, day is 1-7 or Sunday-Saturday, 1 means Sunday

Next_day (sysdate,6) is the next Friday from now on. The latter figures are counted from Sunday.

1 2 3 4 5 6 7

One, two, three, four, five, six.

Select (sysdate-to_date ('2003-12-03 12 hh34:mi:ss' 55 hh34:mi:ss') * 24' 60'60 from ddual

The date returns days and then converts to ss

24 day round [rounded to the nearest date] (rounding to the nearest Sunday)

Select sysdate S1

Round (sysdate) S2

Round (sysdate,'year') YEAR

Round (sysdate,'month') MONTH

Round (sysdate,'day') DAY from dual

25herotrunc [truncated to the nearest date, in days], returns the date type

Select sysdate S1

Trunc (sysdate) S2, / / returns the current date with no time, minutes and seconds

Trunc (sysdate,'year') YEAR, / / returns January 1st of the current year, without time and seconds

Trunc (sysdate,'month') MONTH, / / returns the first day of the current month without time, minutes and seconds

Trunc (sysdate,'day') DAY / / returns the Sunday of the current week, without time and seconds

From dual

26, returns the latest date in the date list

Select greatest (October-February-04') from dual

twenty-seven。 Calculate the time difference

Note: the time difference of oracle is measured in days, so it is converted into years, months and days.

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss')) /) as spanYears from dual / / time difference-year

Select ceil (moths_between (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss') as spanMonths from dual / time difference-month

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss') as spanDays from dual / time difference-day

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss')) * 24) as spanHours from dual / / time difference

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss')) * 24060) as spanMinutes from dual / / time difference

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss') * 24 hours 60 minutes 60) as spanSeconds from dual / / time difference-second

twenty-eight。 Update time

Note: the addition and subtraction of oracle time is in days, and the variable is set to n, so it is converted into year, month and day.

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n*365,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-year

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), add_months (sysdate,n) as newTime from dual / / change time-month

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-day

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n/24,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-hour

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n/24/60,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-min

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n/24/60/60,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-second

twenty-nine。 Find the first and last day of the month

SELECT Trunc (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

three。 Character function (can be used for literal characters or database columns)

1, string interception

Select substr ('abcdef',1,3) from dual

2. Find the location of the substring

Select instr ('abcfdgfdhd','fd') from dual

3, string concatenation

Select 'HELLO' | |' hello world' from dual

4, 1) remove the spaces in the string

Select ltrim ('abc') S1

Rtrim ('zhang') S2

Trim ('zhang') S3 from dual

2) remove prefixes and suffixes

Select trim (leading 9 from 9998767999) S1

Trim (trailing 9 from 9998767999) S2

Trim (9 from 9998767999) S3 from dual

5, returns the Ascii value of the first letter of the string

Select ascii ('a') from dual

6, returns the letter corresponding to the ascii value

Select chr (97) from dual

7, calculate the length of the string

Select length ('abcdef') from dual

8 lower (uppercase), lower (lowercase), upper (uppercase)

Select lower ('ABC') S1

Upper ('def') S2

Initcap ('efg') S3

From dual

9,Replace

Select replace ('abc','b','xy') from dual

10,translate

Select translate ('abc','b','xx') from dual;-- x is 1 bit

11 rpad [right fill] (used to control the output format)

Select lpad ('func',15,'=') S1, rpad (' func',15,'-') S2 from dual

Select lpad (dname,14,'=') from dept

12, decode [implements if.. then logic] Note: the first is an expression, and the last is a value that does not meet any of the conditions.

Select deptno,decode (deptno,10,'1',20,'2',30,'3',' other') from dept

Example:

Select seed,account_name,decode (seed,111,1000,200,2000,0) from t_userInfo// if seed is 111, take 1000,200,2000,0.

Select seed,account_name,decode (sign (seed-111), 1grammatical big seed',-1,'little seed','equal seed') from t_userInfo// shows large if seed > 111and small if seed > 111and others show

Show equality

13 case [implement switch.. case logic]

SELECT CASE X-FIELD

WHEN X-FIELD

< 40 THEN 'X-FIELD 小于 40' WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50' WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60' ELSE 'UNBEKNOWN' END FROM DUAL 注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时，用Decode更为简洁。 四.数字函数 1，取整函数（ceil 向上取整,floor 向下取整） select ceil(66.6) N1,floor(66.6) N2 from dual; 2, 取幂(power) 和 求平方根(sqrt) select power(3,2) N1,sqrt(9) N2 from dual; 3,求余 select mod(9,5) from dual; 4,返回固定小数位数 (round:四舍五入，trunc:直接截断) select round(66.667,2) N1,trunc(66.667,2) N2 from dual; 5,返回值的符号(正数返回为1,负数为-1) select sign(-32),sign(293) from dual; 五.转换函数 1，to_char()[将日期和数字类型转换成字符类型] 1) select to_char(sysdate) s1, to_char(sysdate,'yyyy-mm-dd') s2, to_char(sysdate,'yyyy') s3, to_char(sysdate,'yyyy-mm-dd hh22:mi:ss') s4, to_char(sysdate, 'hh34:mi:ss') s5, to_char(sysdate,'DAY') s6 from dual; 2) select sal,to_char(sal,'＄99999') n1,to_char(sal,'＄99,999') n2 from emp 2, to_date()[将字符类型转换为日期类型] insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd')); 3, to_number() 转换为数字类型 select to_number(to_char(sysdate,'hh22')) from dual; //以数字显示的小时数 六.其他函数 1.user: 返回登录的用户名称 select user from dual; 2.vsize: 返回表达式所需的字节数 select vsize('HELLO') from dual; 3.nvl(ex1,ex2): 　 ex1值为空则返回ex2,否则返回该值本身ex1（常用） 例：如果雇员没有佣金，将显示0，否则显示佣金 select comm,nvl(comm,0) from emp; 4.nullif(ex1,ex2): 值相等返空，否则返回第一个值 例：如果工资和佣金相等，则显示空，否则显示工资 select nullif(sal,comm),sal,comm from emp; 5.coalesce: 　 返回列表中第一个非空表达式 select comm,sal,coalesce(comm,sal,sal*10) from emp; 6.nvl2(ex1,ex2,ex3) : 如果ex1不为空，显示ex2,否则显示ex3 如：查看有佣金的雇员姓名以及他们的佣金 　　select nvl2(comm,ename,') as HaveCommName,comm from emp; 七.分组函数 max min avg count sum 1，整个结果集是一个组 1) 求部门30 的最高工资，最低工资,平均工资，总人数，有工作的人数，工种数量及工资总和 select max(ename),max(sal), min(ename),min(sal), avg(sal), count(*) ,count(job),count(distinct(job)) , sum(sal) from emp where deptno=30; 2, 带group by 和 having 的分组 1)按部门分组求最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和 select deptno, max(ename),max(sal), min(ename),min(sal), avg(sal), count(*) ,count(job),count(distinct(job)) , sum(sal) from emp group by deptno; 2)部门30的最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和 select deptno, max(ename),max(sal), min(ename),min(sal), avg(sal), count(*) ,count(job),count(distinct(job)) , sum(sal) from emp group by deptno having deptno=30; 3, stddev 返回一组值的标准偏差 select deptno,stddev(sal) from emp group by deptno; variance 返回一组值的方差差 select deptno,variance(sal) from emp group by deptno; 4, 带有rollup和cube操作符的Group By rollup 按分组的第一个列进行统计和最后的小计 cube 按分组的所有列的进行统计和最后的小计 select deptno,job ,sum(sal) from emp group by deptno,job; select deptno,job ,sum(sal) from emp group by rollup(deptno,job); cube 产生组内所有列的统计和最后的小计 select deptno,job ,sum(sal) from emp group by cube(deptno,job); 八、临时表 只在会话期间或在事务处理期间存在的表. 临时表在插入数据时，动态分配空间 create global temporary table temp_dept (dno number, dname varchar2(10)) on commit delete rows; insert into temp_dept values(10,'ABC'); commit; select * from temp_dept; --无数据显示,数据自动清除 on commit preserve rows:在会话期间表一直可以存在（保留数据） on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据） ========================================================================================= 转自：http://www.blogjava.net/sunnyliu/archive/2010/07/14/326084.html Oracle时间日期操作 sysdate+(5/24/60/60) 在系统时间基础上延迟5秒 sysdate+5/24/60 在系统时间基础上延迟5分钟 sysdate+5/24 在系统时间基础上延迟5小时 sysdate+5 在系统时间基础上延迟5天 add_months(sysdate,-5) 在系统时间基础上延迟5月 add_months(sysdate,-5*12) 在系统时间基础上延迟5年 上月末的日期：select last_day(add_months(sysdate, -1)) from dual; 本月的最后一秒：select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual 本周星期一的日期：select trunc(sysdate,'day')+1 from dual 年初至今的天数：select ceil(sysdate - trunc(sysdate, 'year')) from dual; 今天是今年的第几周 :select to_char(sysdate,'fmww') from dual 今天是本月的第几周:SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual 本月的天数 SELECT to_char(last_day(SYSDATE),'dd') days FROM dual 今年的天数 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 下个星期一的日期 SELECT Next_day(SYSDATE,'monday') FROM dual ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ --计算工作日方法 create table t(s date,e date); alter session set nls_date_format = 'yyyy-mm-dd'; insert into t values('2003-03-01','2003-03-03'); insert into t values('2003-03-02','2003-03-03'); insert into t values('2003-03-07','2003-03-08'); insert into t values('2003-03-07','2003-03-09'); insert into t values('2003-03-05','2003-03-07'); insert into t values('2003-02-01','2003-03-31'); -- 这里假定日期都是不带时间的，否则在所有日期前加trunc即可。 select s,e,e-s+1 total_days, trunc((e-s+1)/7)*5 + length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')) work_days from t; -- drop table t; 引此：http://www.itpub.net/showthread.php?s=1635506cd5f48b1bc3adbe4cde96f227&threadid=104060&perpage=15&pagenumber=1 ================================================================================ 判断当前时间是上午下午还是晚上 SELECT CASE WHEN to_number(to_char(SYSDATE,'hh34')) BETWEEN 6 AND 11 THEN '上午' WHEN to_number(to_char(SYSDATE,'hh34')) BETWEEN 11 AND 17 THEN '下午' WHEN to_number(to_char(SYSDATE,'hh34')) BETWEEN 17 AND 21 THEN '晚上' END FROM dual; ================================================================================ Oracle 中的一些处理日期 将数字转换为任意时间格式.如秒:需要转换为天/小时 SELECT to_char(floor(TRUNC(936000/(60*60))/24))||'天'||to_char(mod(TRUNC(936000/(60*60)),24))||'小时' FROM DUAL TO_DATE格式 Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out march Year: yy two digits 98 yyyy four digits 1998 24小时格式下时间范围为： 0:00:00 - 23:59:59.... 12小时格式下时间范围为： 1:00:00 - 12:59:59 .... 1. 日期和字符转换函数用法（to_date,to_char） 2. select to_char( to_date(222,'J'),'Jsp') from dual 显示Two Hundred Twenty-Two 3. 求某天是星期几 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 星期一 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; monday 设置日期语言 ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 也可以这样 TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') 4. 两个日期间的天数 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; 5. 时间为null的用法 select id, active_date from table1 UNION select 1, TO_DATE(null) from dual; 注意要用TO_DATE(null) 6. a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd') 那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。 所以，当时间需要精确的时候，觉得to_char还是必要的 7. 日期格式冲突问题 输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01' alter system set NLS_DATE_LANGUAGE = American alter session set NLS_DATE_LANGUAGE = American 或者在to_date中写 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; 注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， 可查看 select * from nls_session_parameters select * from V$NLS_PARAMETERS 8. select count(*) from ( select rownum-1 rnum from all_objects where rownum select sysdate ,to_char(sysdate,'hh') from dual; SYSDATE TO_CHAR(SYSDATE,'HH') -------------------- --------------------- 2003-10-13 19:35:21 07 SQL>

Select sysdate, to_char (sysdate,'hh34') from dual

SYSDATE TO_CHAR (SYSDATE,'HH24')

2003-10-13 19:35:21 19

Get year, month, day, similar to this

13.

The treatment of the year, month and day

Select older_date

Newer_date

Years

Months

Abs (

Trunc (

Newer_date-

Add_months (older_date,years*12+months)

)

) days

From (select

Trunc (months_between (newer_date, older_date) / 12) YEARS

Mod (trunc (months_between (newer_date, older_date))

12) MONTHS

Newer_date

Older_date

From (select hiredate older_date

Add_months (hiredate,rownum) + rownum newer_date

From emp)

)

14.

The way to deal with the variable number of days of the month

Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

16.

Find out the number of days this year

Select add_months (trunc (sysdate,'year'), 12)-trunc (sysdate,'year') from dual

The treatment of leap years

To_char (last_day (to_date ('02' rooms: year,'mmyyyy')),' dd')

If it's 28, it's not a leap year.

17.

The difference between yyyy and rrrr

'YYYY99 TO_C

-

Yyyy 99 0099

Rrrr 99 1999

Yyyy 01 0001

Rrrr 01 2001

18. Treatment of different time zones

Select to_char (NEW_TIME (sysdate, 'GMT','EST'),' dd/mm/yyyy hh:mi:ss'), sysdate

From dual

19.

Five seconds at an interval

Select TO_DATE (FLOOR (TO_CHAR (sysdate,'SSSSS') / 300) * 300), TO_CHAR (sysdate,'SSSSS')

From dual

2002-11-1 9:55:00 35786

SSSSS represents 5-digit seconds

20.

The day of the year

Select TO_CHAR (SYSDATE,'DDD'), sysdate from dual

310 2002-11-6 10:03:51

21. Calculate hours, minutes, seconds, milliseconds

Select

Days

A

TRUNC (Atom 24) Hours

TRUNC (Atom 24060-60*TRUNC (Atom 24)) Minutes

TRUNC (Av2406060-60*TRUNC (A24060)) Seconds

TRUNC (A24 / 60 / 60 / 100-100*TRUNC) mSeconds

From

(

Select

Trunc (sysdate) Days

Sysdate-trunc (sysdate) A

From dual

)

Select * from tabname

Order by decode (mode,'FIFO',1,-1) * to_char (rq,'yyyymmddhh34miss')

/ /

Floor ((date2-date1) / 365) as a year

Floor ((date2-date1, 365) / 30) as month

Mod (mod (date2-date1, 365,30) as a day.

23.next_day function

Next_day (sysdate,6) is the next Friday from now on. The latter figures are counted from Sunday.

1 2 3 4 5 6 7

One, two, three, four, five, six.

-

Oracle's operation on time / date

There are many functions about dates in oracle, such as:

1. Add_months () is used to increase or decrease some months from a date value

Date_value:=add_months (date_value,number_of_months)

Example:

SQL > select add_months (sysdate,12) "Next Year" from dual

Next Year

-

13-November-04

SQL > select add_months (sysdate,112) "Last Year" from dual

Last Year

-

13-March-13

SQL >

2. Current_date () returns the current date in the current time zone

Date_value:=current_date

SQL > column sessiontimezone for A15

SQL > select sessiontimezone,current_date from dual

SESSIONTIMEZONE CURRENT_DA

--

+ 08:00 13-11-03

SQL > alter session set time_zone='-11:00'

2 /

The session has changed.

SQL > select sessiontimezone,current_timestamp from dual

SESSIONTIMEZONE CURRENT_TIMESTAMP

-11:00 12-November-03 04.59.13.668000 p.m.-11:

00

SQL >

3. Current_timestamp () returns the current date in the current time zone as the timestamp with time zone data type

Timestamp_with_time_zone_value:=current_timestamp ([timestamp_precision])

SQL > column sessiontimezone for A15

SQL > column current_timestamp format A36

SQL > select sessiontimezone,current_timestamp from dual

SESSIONTIMEZONE CURRENT_TIMESTAMP

+ 08:00 13-November-03 11.56.28.160000 AM + 08:

00

SQL > alter session set time_zone='-11:00'

2 /

The session has changed.

SQL > select sessiontimezone,current_timestamp from dual

SESSIONTIMEZONE CURRENT_TIMESTAMP

-11:00 12-November-03 04.58.00.243000 p.m.-11:

00

SQL >

4. Dbtimezone () returns the time zone

Varchar_value:=dbtimezone

SQL > select dbtimezone from dual

DBTIME

-

07:00

SQL >

5. Extract () finds the field value of the date or interval value

Date_value:=extract (date_field from [datetime_value | interval_value])

SQL > select extract (month from sysdate) "This Month" from dual

This Month

-

eleven

SQL > select extract (year from add_months (sysdate,36)) "3 Years Out" from dual

3 Years Out

-

2006

SQL >

6. Last_day () returns the date of the last day of the month that contains the date parameter

Date_value:=last_day (date_value)

SQL > select last_day (date'2000-02-01') "Leap Yr?" From dual

Leap Yr?

-

29-February-00

SQL > select last_day (sysdate) "Last day of this month" from dual

Last day o

-

30-November-03

SQL >

7. Localtimestamp () returns the date and time in the session

Timestamp_value:=localtimestamp

SQL > column localtimestamp format A28

SQL > select localtimestamp from dual

LOCALTIMESTAMP

--

13-November-03 12.09.15.433000

Afternoon

SQL > select localtimestamp,current_timestamp from dual

LOCALTIMESTAMP CURRENT_TIMESTAMP

13-November-03 12.09.31.006000 13-November-03 12.09.31.006000 PM + 08:

00 p.m.

SQL > alter session set time_zone='-11:00'

The session has changed.

SQL > select localtimestamp,to_char (sysdate,'DD-MM-YYYY HH:MI:SS AM') "SYSDATE" from dual

LOCALTIMESTAMP SYSDATE

-

December-November-03 05.11.31.259000 13-11-2003 12:11:31 afternoon

Afternoon

SQL >

8. Months_between () judges the number of months between two dates

Number_value:=months_between (date_value,date_value)

SQL > select months_between (sysdate,date'1971-05-18') from dual

MONTHS_BETWEEN (SYSDATE,DATE'1971-05-18')

--

389.855143

SQL > select months_between (sysdate,date'2001-01-01') from dual

MONTHS_BETWEEN (SYSDATE,DATE'2001-01-01')

--

34.4035409

SQL >

9. Next_day (), given a date value, returns the date value in which the day indicated by the second parameter appears for the first time (the name string of the corresponding day should be returned)

Description:

Single-line date function

Single-line date functions operate on data data types, most of which have parameters of data data type, and the vast majority return values of data data type.

Add_months (,)

Returns the result after d plus I months. I can make any integer. If I is a decimal, then the database implicitly converts it to an integer, which will truncate the part after the decimal point.

Last_day ()

Function returns the last day of the month containing the date d

Months_between (,)

Returns the number of months between D1 and D2. If the days of D1 and D2 are the same, or both make the last day of the month, an integer will be returned, otherwise the result will contain a score.

New_time (,)

D1 is a date data type. The date and time in the current zone tz1 are d hours, and the date and time in the time zone tz2 are returned. String when tz1 and tz2.

Next_day (,)

On the first day of the condition given by dow after the return date d, dow specifies a day of the week in the language given in the current session, and the time component returned is the same as the time component of d.

Select next_day ('01Mujanmur2000') "1st monday", next_day ('01Murjanmuri 2004') + 7 "2nd tuesday") from dual;1st monday 2nd tuesday03-jan-2000 09-nov-2004

Round ([,])

Rounds the date d in the format specified by fmt, with fmt as a string.

Syadate

Function takes no arguments and returns the current date and time.

Trunc ([,])

Returns the date of the unit specified by fmt d.

One-line conversion function

Single-line conversion functions are used to manipulate multiple data types, converting between data types.

Chartorwid ()

C makes a string, and the function converts c to the rwid data type.

Select test_id from test_case where rowid=chartorwid (''aaaa0saacaaaaliaaa'')

Convert (, [])

C trailing string. Dset and sset are two character sets. The function converts the string c from the sset character set to the dset character set. The default setting of sset is the database character set.

Hextoraw ()

X is a hexadecimal string and the function converts hexadecimal x to the raw data type.

Rawtohex ()

X is the raw data type string, and the function converts the raw data class to a hexadecimal data type.

Rowidtochar ()

Function to convert the rowid data type to the char data type.

To_char ([,)

X is a data or number data type, and the function converts x to the char data type in the format specified by fmt, if x is the date nlsparm=nls_date_language controls the language in which the month and day are returned. If x is the number nlsparm=nls_numeric_characters is used to specify the delimiter of decimal and thousand places, as well as the currency symbol.

Nls_numeric_characters = "dg", nls_currency= "string"

To_date ([,])

C represents a string and fmt represents a string in a special format. Returns the language used for the cdepartment nlsparm representation in fmt format. Function to convert the string c to the date data type.

To_multi_byte ()

C represents a string, and the function converts the burden truncation of c into multi-byte characters.

To_number ([,])

C represents a string, fmt represents a specially formatted string, and the return value of the function is displayed in the format specified by fmt. Nlsparm represents the language, and the function returns the number represented by c.

To_single_byte ()

Converts the multi-byte characters in the string c into equivalent single-byte characters. This function is used only if the database character set contains both single-byte and multi-byte characters.

Oracle's operation on time / date

1. Date-time interval operation

Current time minus 7 minutes

Select sysdate,sysdate-interval'7' MINUTE from dual

Current time minus 7 hours of time

Select sysdate-interval'7' hour from dual

Current time minus 7 days

Select sysdate-interval'7' day from dual

Current time minus July time

Select sysdate,sysdate-interval'7' month from dual

Current time minus 7 years

Select sysdate,sysdate-interval'7' year from dual

Time interval multiplied by a number

Select sysdate,sysdate-8 * interval'2' hour from dual

two。 Date to character operation

Select sysdate,to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual

Select sysdate,to_char (sysdate,'yyyy-mm-dd hh:mi:ss') from dual

Select sysdate,to_char (sysdate,'yyyy-ddd hh:mi:ss') from dual

Select sysdate,to_char (sysdate,'yyyy-mm iw-d hh:mi:ss') from dual

Refer to the relevant documentation of oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)

3. Character-to-date operation

Select to_date ('2003-10-17 21-21-15-15-15-15-15-15-17-10-17-21-15-15-15-15-17-15-15-15-15-15-17-15-15-15-17-21-15-15-15-15-17-21-15-15-15-15-15-17-21-15-15-15-15-15-15-15-15-15-15-17-10-17 21-15-15 from dual (' 2003-10-17 21, 15-15, 15, 15, 15, 15, 14, 15, 15, 14, 15, 14, 15, 14, 15, 14, 15, 14, 14, 14, 14, 14, 21, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15,

The specific usage is similar to that of to_char above.

4. The use of trunk/ ROUND function

Select trunc (sysdate, 'YEAR') from dual

Select trunc (sysdate) from dual

Select to_char (trunc (sysdate, 'YYYY'),' YYYY') from dual

5.oracle has millisecond data types

-- returns the current time, year, month, day, hour, minute, second, millisecond

Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24:MI:SSxFF') from dual

-- returns the milliseconds of the current time. You can specify the precision after the seconds (maximum = 9).

Select to_char (current_timestamp (9), 'MI:SSxFF') from dual

6. Calculate the time the program runs (ms)

Declare

Type rc is ref cursor

L_rc rc

L_dummy all_objects.object_name%type

L_start number default dbms_utility.get_time

Begin

For I in 1.. one thousand

Loop

Open l_rc for

'select object_name from all_objects' | |

'where object_id =' | | I

Fetch l_rc into l_dummy

Close l_rc

End loop

Dbms_output.put_line

(round ((dbms_utility.get_time-l_start) / 100,2) | |

'seconds...')

End

Thank you for your reading, the above is the content of "ORACLE date-time function Daquan", after the study of this article, I believe you have a deeper understanding of the problem of ORACLE date-time function Daquan, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.