Oracle 日期时间函数的用法复习进程.doc

《Oracle 日期时间函数的用法复习进程.doc》由会员分享，可在线阅读，更多相关《Oracle 日期时间函数的用法复习进程.doc（31页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Good is good, but better carries it.精益求精，善益求善。Oracle 日期时间函数的用法-Oracle日期时间函数的用法在oracle中处理日期大全TO_DATE格式Day:ddnumber12dyabbreviatedfridayspelledoutfridayddspthspelledout,ordinaltwelfthMonth:mmnumber03monabbreviatedmarmonthspelledoutmarchYear:yytwodigits98yyyyfourdigits1998在oracle中处理日期大全TO_DATE格式Day:ddn

2、umber12dyabbreviatedfridayspelledoutfridayddspthspelledout,ordinaltwelfthMonth:mmnumber03monabbreviatedmarmonthspelledoutmarchYear:yytwodigits98yyyyfourdigits199824小时格式下时间范围为：0:00:00-23:59:59.12小时格式下时间范围为：1:00:00-12:59:59.1.日期和字符转换函数用法（to_date,to_char）2.selectto_char(to_date(222,J),Jsp)fromdual显示Two

3、HundredTwenty-Two3.求某天是星期几selectto_char(to_date(2002-08-26,yyyy-mm-dd),day)fromdual;星期一selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual;monday设置日期语言ALTERSESSIONSETNLS_DATE_LANGUAGE=AMERICAN;也可以这样TO_DATE(2002-08-26,YYYY-mm-dd,NLS_DATE_LANGUAGE=American)4.两个日期间的天数se

4、lectfloor(sysdate-to_date(20020405,yyyymmdd)fromdual;5.时间为null的用法selectid,active_datefromtable1UNIONselect1,TO_DATE(null)fromdual;注意要用TO_DATE(null)6.a_datebetweento_date(20011201,yyyymmdd)andto_date(20011231,yyyymmdd)那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。所以，当时间需要精确的时候，觉得to_char还是必要的7.日期格式冲突问题输入的格式

5、要看你安装的ORACLE字符集的类型,比如:US7ASCII,date格式的类型就是:01-Jan-01altersystemsetNLS_DATE_LANGUAGE=AmericanaltersessionsetNLS_DATE_LANGUAGE=American或者在to_date中写selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual;注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，可查看select*fromnls_session_parameterss

6、elect*fromV$NLS_PARAMETERS8.selectcount(*)from(selectrownum-1rnumfromall_objectswhererownumselectsysdate,to_char(sysdate,hh)fromdual;SYSDATETO_CHAR(SYSDATE,HH)-2003-10-1319:35:2107SQLselectsysdate,to_char(sysdate,hh24)fromdual;SYSDATETO_CHAR(SYSDATE,HH24)-2003-10-1319:35:2119获取年月日与此类似13.年月日的处理select

7、older_date,newer_date,years,months,abs(trunc(newer_date-add_months(older_date,years*12+months)daysfrom(selecttrunc(months_between(newer_date,older_date)/12)YEARS,mod(trunc(months_between(newer_date,older_date),12)MONTHS,newer_date,older_datefrom(selecthiredateolder_date,add_months(hiredate,rownum)+r

8、ownumnewer_datefromemp)14.处理月份天数不定的办法selectto_char(add_months(last_day(sysdate)+1,-2),yyyymmdd),last_day(sysdate)fromdual16.找出今年的天数selectadd_months(trunc(sysdate,year),12)-trunc(sysdate,year)fromdual闰年的处理方法to_char(last_day(to_date(02|:year,mmyyyy),dd)如果是28就不是闰年17.yyyy与rrrr的区别YYYY99TO_C-yyyy990099rrr

9、r991999yyyy010001rrrr01200118.不同时区的处理selectto_char(NEW_TIME(sysdate,GMT,EST),dd/mm/yyyyhh:mi:ss),sysdatefromdual;19.5秒钟一个间隔SelectTO_DATE(FLOOR(TO_CHAR(sysdate,SSSSS)/300)*300,SSSSS),TO_CHAR(sysdate,SSSSS)fromdual2002-11-19:55:0035786SSSSS表示5位秒数20.一年的第几天selectTO_CHAR(SYSDATE,DDD),sysdatefromdual31020

10、02-11-610:03:5121.计算小时,分,秒,毫秒selectDays,A,TRUNC(A*24)Hours,TRUNC(A*24*60-60*TRUNC(A*24)Minutes,TRUNC(A*24*60*60-60*TRUNC(A*24*60)Seconds,TRUNC(A*24*60*60*100-100*TRUNC(A*24*60*60)mSecondsfrom(selecttrunc(sysdate)Days,sysdate-trunc(sysdate)Afromdual)select*fromtabnameorderbydecode(mode,FIFO,1,-1)*to_

11、char(rq,yyyymmddhh24miss);/floor(date2-date1)/365)作为年floor(date2-date1,365)/30)作为月mod(mod(date2-date1,365),30)作为日.23.next_day函数next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。1234567日一二三四五六oracle中有很多关于日期的函数在oracle中有很多关于日期的函数，如：1、add_months()用于从一个日期值增加或减少一些月份date_value:=add_months(date_value,number_of

12、_months)例：SQLselectadd_months(sysdate,12)NextYearfromdual;NextYear-13-11月-04SQLselectadd_months(sysdate,112)LastYearfromdual;LastYear-13-3月-13SQL2、current_date()返回当前会放时区中的当前日期date_value:=current_dateSQLcolumnsessiontimezonefora15SQLselectsessiontimezone,current_datefromdual;SESSIONTIMEZONECURRENT_DA

13、-+08:0013-11月-03SQLaltersessionsettime_zone=-11:002/会话已更改。SQLselectsessiontimezone,current_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP-11:0012-11月-0304.59.13.668000下午-11:00SQL3、current_timestamp()以timestampwithtimezone数据类型返回当前会放时区中的当前日期timestamp_with_time_zone_value:=current_timestamp(timesta

14、mp_precision)SQLcolumnsessiontimezonefora15SQLcolumncurrent_timestampformata36SQLselectsessiontimezone,current_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP-+08:0013-11月-0311.56.28.160000上午+08:00SQLaltersessionsettime_zone=-11:002/会话已更改。SQLselectsessiontimezone,current_timestampfromdual;SESSION

15、TIMEZONECURRENT_TIMESTAMP-11:0012-11月-0304.58.00.243000下午-11:00SQL4、dbtimezone()返回时区varchar_value:=dbtimezoneSQLselectdbtimezonefromdual;DBTIME-07:00SQL5、extract()找出日期或间隔值的字段值date_value:=extract(date_fieldfromdatetime_value|interval_value)SQLselectextract(monthfromsysdate)ThisMonthfromdual;ThisMonth

16、-11SQLselectextract(yearfromadd_months(sysdate,36)3YearsOutfromdual;3YearsOut-2006SQL6、last_day()返回包含了日期参数的月份的最后一天的日期date_value:=last_day(date_value)SQLselectlast_day(date2000-02-01)LeapYr?fromdual;LeapYr?-29-2月-00SQLselectlast_day(sysdate)Lastdayofthismonthfromdual;Lastdayo-30-11月-03SQL数据库基础：SQLSer

17、ver基本函数介绍时间:2010-03-0716:29来源:草根站长作者:编辑整理责任编辑:冰山以北我要投稿1.字符串函数长度与分析用datalength(Char_expr)返回字符串包含字符数,但不包含后面的空格substring(expression,start,length)不多说了,取子串right(char_expr,int_expr)返回字符串右边int_expr个字符字符操作类upper(char_expr)转为大写lower(ch1.字符串函数长度与分析用datalength(Char_expr)返回字符串包含字符数,但不包含后面的空格substring(expression

18、,start,length)不多说了,取子串right(char_expr,int_expr)返回字符串右边int_expr个字符字符操作类upper(char_expr)转为大写lower(char_expr)转为小写space(int_expr)生成int_expr个空格replicate(char_expr,int_expr)复制字符串int_expr次reverse(char_expr)反转字符串stuff(char_expr1,start,length,char_expr2)将字符串char_expr1中的从start开始的length个字符用char_expr2代替ltrim(ch

19、ar_expr)rtrim(char_expr)取掉空格ascii(char)char(ascii)两函数对应,取ascii码,根据ascii吗取字符字符串查找charindex(char_expr,expression)返回char_expr的起始位置patindex(%pattern%,expression)返回指定模式的起始位置,否则为02.数学函数abs(numeric_expr)求绝对值ceiling(numeric_expr)取大于等于指定值的最小整数exp(float_expr)取指数floor(numeric_expr)小于等于指定值得最大整数pi()3.1415926.pow

20、er(numeric_expr,power)返回power次方rand(int_expr)随机数产生器round(numeric_expr,int_expr)安int_expr规定的精度四舍五入sign(int_expr)根据正数,0,负数,返回+1,0,-1sqrt(float_expr)平方根3.日期函数getdate()返回日期datename(datepart,date_expr)返回名称如Junedatepart(datepart,date_expr)取日期一部份datediff(datepart,date_expr1.dateexpr2)日期差dateadd(datepart,nu

21、mber,date_expr)返回日期加上number上述函数中datepart的写法取值和意义yy1753-9999年份qq1-4刻mm1-12月dy1-366日dd1-31日wk1-54周dw1-7周几hh0-23小时mi0-59分钟ss0-59秒ms0-999毫秒日期转换convert()4.系统函数suser_name()用户登录名user_name()用户在数据库中的名字user用户在数据库中的名字show_role()对当前用户起作用的规则db_name()数据库名object_name(obj_id)数据库对象名col_name(obj_id,col_id)列名col_lengt

22、h(objname,colname)列长度valid_name(char_expr)是否是有效标识符SQLServer中的时间算法总结DECLAREDateDATETIMESETDate=GETDATE()-前一天，给定日期的前一天SELECTDATEADD(DAY,-1,Date)AS前一天-后一天，给定日期的后一天SELECTDATEADD(DAY,1,Date)AS后一天GO-月初，计算给定日期所在月的第一天-这DECLAREDateDATETIMESETDate=GETDATE()-前一天，给定日期的前一天SELECTDATEADD(DAY,-1,Date)AS前一天-后一天，给定日期

23、的后一天SELECTDATEADD(DAY,1,Date)AS后一天GO-月初，计算给定日期所在月的第一天-这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用-来计算很多不同的日期。DECLAREDateDATETIMESETDate=GETDATE()SELECTDATEADD(MONTH,DATEDIFF(MONTH,1900-01-01,Date),1900-01-01)AS所在月的第一天-精简算法，根据SQLServer的时间表示方式可知，1900-01-01可以用0代替SELECTDATEADD

24、(MONTH,DATEDIFF(MONTH,0,Date),0)AS所在月的第一天-上面两种算法精确到天时分秒均为00:00:00.000-下面算法课以保留时分秒-思路：用给定日期减去月第一天与给定日期差的天数SELECTDATEADD(DAY,1-DATEPART(DAY,Date),Date)GO-月末，计算给定日期所在月的最后一天DECLAREDateDATETIMESETDate=GETDATE()-思路：当前月的下一月1号在减1天SELECTDATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,1900-01-01,Date),1900-01-0

25、1)AS所在月的最一天SELECTDATEADD(MONTH,1+DATEDIFF(MONTH,1900-01-01,Date),1900-01-01)-1AS所在月的最一天-1900-01-01用0代替SELECTDATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,Date),0)AS所在月的最一天SELECTDATEADD(MONTH,1+DATEDIFF(MONTH,0,Date),0)-1AS所在月的最一天-思路：与月初计算思路相同SELECTDATEADD(MONTH,DATEDIFF(MONTH,1989-12-31,Date),1989

26、-12-31)AS所在月的最一天-精简算法，1989-12-31用-1代替SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,Date),-1)AS所在月的最一天-保留时分秒的算法SELECTDATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)GO-其他月计算-计算给定日期所在月的上月第一天DECLAREDateDATETIMESETDate=GETDATE()-当前月第一天减去一个月SELECTDATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH

27、,0,Date),0)AS上月第一天-简化SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,Date)-1,0)AS上月第一天-另一种当前月第一天算法SELECTDATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)上月第一天GO-计算给定日期所在月的上月最后一天DECLAREDateDATETIMESETDate=GETDATE()-当前月第一天减去一天SELECTDATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS上月最后一天-另一种当前月第一天算法S

28、ELECTDATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)上月最后一天SELECTDATEADD(DAY,1-DATEPART(DAY,Date),Date)-1上月最后一天-另一种算法，不能用当前月的最后一天加一个月，因为当前月可能是30天。-例如SELECTDATEADD(MONTH,1,2010-06-30)-结果是2010-07-30而不是2010-07-31，-这也是月末算法采用下月第一天减1天计算的原因-但是如果计算月是31天择无此问题-例如SELECTDATEADD(MONTH,1,2010-05-31)-结果是2010-

29、06-30-因此下面算法是正确的，-1表示1899-12-3100:00:00.000-SELECTCONVERT(DATETIME,-1)SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,Date)-1,-1)-另一种当前月算法SELECTDATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)上月最后一天-简化SELECTDATEADD(DAY,0-DATEPART(DAY,Date),Date)上月最后一天GO-计算给定日期所在月的下月第一天DECLAREDateDATETIMESETDate=GETDATE()

30、-当前月第一天加一个月SELECTDATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS下月第一天-简化SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,Date)+1,0)AS下月第一天-另一种当前月第一天算法SELECTDATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)下月第一天GO-计算给定日期所在月的下月最后一天DECLAREDateDATETIMESETDate=GETDATE()-当前月第一天加2个月再减去1天SELECTDATEADD(DA

31、Y,-1,DATEADD(MONTH,2,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS下月最后一天-简化SELECTDATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date)+2,0)AS下月最后一天SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,Date)+2,0)-1AS下月最后一天-另一种算法SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,Date)+1,-1)下月最后一天-另一种当前月第一天算法SELECTDATEADD(DAY,-1,DATEADD(

32、MONTH,2,DATEADD(DAY,1-DATEPART(DAY,Date),Date)下月最后一天GO-所在星期的第一天，计算给定日期所在星期的第1天(星期日为第一天)DECLAREDateDATETIMESETDate=GETDATE()-与SQLServer语言版本相关的算法-思路：当前日期+星期日(每周的第1天)与当前日期的差的天数-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SETDATEFIRST7-或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)SELECTDATEADD(WEEKDAY,1-DATEPART(W

33、EEKDAY,Date),Date)AS所在星期的第一天，星期日-星期日，与SQLServer语言版本或DATEFIRST无关-1989-12-31是星期日，1989-12-31再加上(当前日期与1989-12-31差的星期数)个星期SELECTDATEADD(WEEK,DATEDIFF(WEEK,-1,Date),-1)AS所在星期的星期日-或者SELECTDATEADD(WEEK,DATEDIFF(WEEK,6,Date),6)AS所在星期的星期日GO-所在星期的第二天，计算给定日期所在星期的第2天(星期日为第一天)DECLAREDateDATETIMESETDate=GETDATE()-

34、思路：当前日期+星期一(每周的第2天)与当前日期的差的天数-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SETDATEFIRST7-或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)SELECTDATEADD(DAY,2-DATEPART(WEEKDAY,Date),Date)AS所在星期的第二天，星期一-星期一，与SQLServer语言版本或DATEFIRST无关-1900-01-01是星期一，1900-01-01再加上(当前日期与1900-01-01差的星期数)个星期SELECTDATEADD(WEEK,DATEDIFF(WEE

35、K,0,Date),0)AS所在星期的星期一GO-上个星期第一天，计算给定日期所在星期的上一个星期日(星期日为第一天)DECLAREDateDATETIMESETDate=GETDATE()-思路：当前日志所在星期的星期日再减1周-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SETDATEFIRST7-或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)SELECTDATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS上个星期第一天，星期日-一周等于7天SELECTDA

36、TEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS上个星期第一天，星期日-简化SELECTDATEADD(DAY,-6-DATEPART(WEEKDAY,Date),Date)AS上个星期第一天，星期日-上个星期日，与SQLServer语言版本或DATEFIRST无关SELECTDATEADD(WEEK,-1+DATEDIFF(WEEK,-1,Date),-1)AS上个星期日-或者SELECTDATEADD(WEEK,DATEDIFF(WEEK,6,Date),-1)AS上个星期日GO-下个星期第一天，计算给定日期所在星期的下一个

37、星期日(星期日为第一天)DECLAREDateDATETIMESETDate=GETDATE()-思路：当前日志所在星期的星期日再加1周-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SETDATEFIRST7-或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)SELECTDATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS下个星期第一天，星期日-一周等于7天SELECTDATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEEKDAY,Date)

38、,Date)AS下个星期第一天，星期日-简化SELECTDATEADD(DAY,8-DATEPART(WEEKDAY,Date),Date)AS下个星期第一天，星期日-下个星期日，与SQLServer语言版本或DATEFIRST无关SELECTDATEADD(WEEK,1+DATEDIFF(WEEK,-1,Date),-1)AS下个星期日-或者SELECTDATEADD(WEEK,DATEDIFF(WEEK,-1,Date),6)AS下个星期日GO-判断给定日期是星期几DECLAREDateDATETIMESETDate=GETDATE()-DATEPART(WEEKDAY,DATE)的返回值

39、与DATEFIRST相关SETDATEFIRST7-或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)SELECTDATEPART(WEEKDAY,Date)-返回值1-星期日，2-星期一，3-星期二.7-星期六-上面算法与SQL语言版本或DATEFIRST相关-下面算法与SQLServer语言版本或DATEFIRST无关SELECTDATENAME(WEEKDAY,Date)星期GO-年度计算DECLAREDateDATETIMESETDate=GETDATE()-年初，计算给定日期所在年的第一天SELECTDATEADD(YEAR,DATEDIFF(YEAR,

40、0,Date),0)AS所在年的第一天-年末，计算给定日期所在年的最后一天SELECTDATEADD(YEAR,DATEDIFF(YEAR,-1,Date),-1)AS所在年的最后一天-上一年年初，计算给定日期所在年的上一年的第一天SELECTDATEADD(YEAR,DATEDIFF(YEAR,-0,Date)-1,0)AS所在年的上一年的第一天-上一年年末，计算给定日期所在年的上一年的最后一天SELECTDATEADD(YEAR,DATEDIFF(YEAR,0,Date),-1)AS所在年的上一年的最后一天-下一年年初，计算给定日期所在年的下一年的第一天SELECTDATEADD(YEAR

41、,1+DATEDIFF(YEAR,0,Date),0)AS所在年的下一年的第一天-下一年年末，计算给定日期所在年的下一年的最后一天SELECTDATEADD(YEAR,1+DATEDIFF(YEAR,-1,Date),-1)AS所在年的下一年的最后一天GO-季度计算DECLAREDateDATETIMESETDate=GETDATE()-季度初，计算给定日期所在季度的第一天SELECTDATEADD(QUARTER,DATEDIFF(QUARTER,0,Date),0)AS当前季度的第一天-季度末，计算给定日期所在季度的最后一天SELECTDATEADD(QUARTER,1+DATEDIFF(QUARTER,0,Date),-1)AS当前季度的最后一天-上个季度初SELECTDATEADD(QUARTER,DATEDIFF(QUARTER,0,Date)-1,0)AS当前季度的上个季度初-上个季度末SELECTDATEADD(QUARTER,DATEDIFF(QUARTER,0,Date),-1)AS当前季度的上个季度末-下个季度初SELECTDATEADD(QUARTER,1+DATEDIFF(QUARTER,0,Date),0)AS当前季度的下个季度初-下个季度末SELECTDATEADD(QUARTER,2+DATEDIFF(QUARTER,0,Dat