Oracle 日期時間函數(shù)的用法復(fù)習(xí)進程

1、Good is good, but better carries it.精益求精，善益求善。Oracle 日期時間函數(shù)的用法-HYPERLINK/html/30/36/38/2010/02/20/88271.htmlOracle日期時間函數(shù)的用法在oracle中處理日期大全TO_DATE格式Day:ddnumber12dyabbreviatedfridayspelledoutfridayddspthspelledout,ordinaltwelfthMonth:mmnumber03monabbreviatedmarmonthspelledoutmarchYear:yytwodigits98yyy

2、yfourdigits1998在oracle中處理日期大全TO_DATE格式Day:ddnumber12dyabbreviatedfridayspelledoutfridayddspthspelledout,ordinaltwelfthMonth:mmnumber03monabbreviatedmarmonthspelledoutmarchYear:yytwodigits98yyyyfourdigits199824小時格式下時間范圍為：0:00:00-23:59:59.12小時格式下時間范圍為：1:00:00-12:59:59.1.日期和字符轉(zhuǎn)換函數(shù)用法（to_date,to_char）2.s

3、electto_char(to_date(222,J),Jsp)fromdual顯示TwoHundredTwenty-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設(shè)置日期語言ALTERSESSIONSETNLS_DATE_LANGUAGE=AMERICAN;也可以這樣TO_DATE(2002-08-26,YYYY-

4、mm-dd,NLS_DATE_LANGUAGE=American)4.兩個日期間的天數(shù)selectfloor(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點之前是不包含在這個范圍之內(nèi)

5、的。所以，當(dāng)時間需要精確的時候，覺得to_char還是必要的7.日期格式?jīng)_突問題輸入的格式要看你安裝的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

6、，當(dāng)然還有很多，可查看select*fromnls_session_parametersselect*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)

7、-2003-10-1319:35:2119獲取年月日與此類似13.年月日的處理selectolder_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(selecthir

8、edateolder_date,add_months(hiredate,rownum)+rownumnewer_datefromemp)14.處理月份天數(shù)不定的辦法selectto_char(add_months(last_day(sysdate)+1,-2),yyyymmdd),last_day(sysdate)fromdual16.找出今年的天數(shù)selectadd_months(trunc(sysdate,year),12)-trunc(sysdate,year)fromdual閏年的處理方法to_char(last_day(to_date(02|:year,mmyyyy),dd)如果是2

9、8就不是閏年17.yyyy與rrrr的區(qū)別YYYY99TO_C-yyyy990099rrrr991999yyyy010001rrrr01200118.不同時區(qū)的處理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位秒數(shù)20.一年的第幾天se

10、lectTO_CHAR(SYSDATE,DDD),sysdatefromdual3102002-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

11、*fromtabnameorderbydecode(mode,FIFO,1,-1)*to_char(rq,yyyymmddhh24miss);/floor(date2-date1)/365)作為年floor(date2-date1,365)/30)作為月mod(mod(date2-date1,365),30)作為日.23.next_day函數(shù)next_day(sysdate,6)是從當(dāng)前開始下一個星期五。后面的數(shù)字是從星期日開始算起。1234567日一二三四五六oracle中有很多關(guān)于日期的函數(shù)在oracle中有很多關(guān)于日期的函數(shù)，如：1、add_months()用于從一個日期值增加或減少一些

12、月份date_value:=add_months(date_value,number_of_months)例：SQLselectadd_months(sysdate,12)NextYearfromdual;NextYear-13-11月-04SQLselectadd_months(sysdate,112)LastYearfromdual;LastYear-13-3月-13SQL2、current_date()返回當(dāng)前會放時區(qū)中的當(dāng)前日期date_value:=current_dateSQLcolumnsessiontimezonefora15SQLselectsessiontimezone,c

13、urrent_datefromdual;SESSIONTIMEZONECURRENT_DA-+08:0013-11月-03SQLaltersessionsettime_zone=-11:002/會話已更改。SQLselectsessiontimezone,current_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP-11:0012-11月-03068000下午-11:00SQL3、current_timestamp()以timestampwithtimezone數(shù)據(jù)類型返回當(dāng)前會放時區(qū)中的當(dāng)前日期timestamp_with_time_z

14、one_value:=current_timestamp(timestamp_precision)SQLcolumnsessiontimezonefora15SQLcolumncurrent_timestampformata36SQLselectsessiontimezone,current_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP-+08:0013-11月-0360000上午+08:00SQLaltersessionsettime_zone=-11:002/會話已更改。SQLselectsessiontimezone,current

15、_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP-11:0012-11月-0304.58.00.243000下午-11:00SQL4、dbtimezone()返回時區(qū)varchar_value:=dbtimezoneSQLselectdbtimezonefromdual;DBTIME-07:00SQL5、extract()找出日期或間隔值的字段值date_value:=extract(date_fieldfromdatetime_value|interval_value)SQLselectextract(monthfromsysdate)T

16、hisMonthfromdual;ThisMonth-11SQLselectextract(yearfromadd_months(sysdate,36)3YearsOutfromdual;3YearsOut-2006SQL6、last_day()返回包含了日期參數(shù)的月份的最后一天的日期date_value:=last_day(date_value)SQLselectlast_day(date2000-02-01)LeapYr?fromdual;LeapYr?-29-2月-00SQLselectlast_day(sysdate)Lastdayofthismonthfromdual;Lastday

17、o-30-11月-03SQLHYPERLINK/html/30/36/38/2010/03/07/91481.html數(shù)據(jù)庫基礎(chǔ)：SQLServer基本函數(shù)介紹時間:2010-03-0716:29來源:草根站長作者:編輯整理責(zé)任編輯:冰山以北HYPERLINK/contribute/t_blank我要投稿1.字符串函數(shù)長度與分析用datalength(Char_expr)返回字符串包含字符數(shù),但不包含后面的空格substring(expression,start,length)不多說了,取子串right(char_expr,int_expr)返回字符串右邊int_expr個字符字符操作類upp

18、er(char_expr)轉(zhuǎn)為大寫lower(ch1.字符串函數(shù)長度與分析用datalength(Char_expr)返回字符串包含字符數(shù),但不包含后面的空格substring(expression,start,length)不多說了,取子串right(char_expr,int_expr)返回字符串右邊int_expr個字符字符操作類upper(char_expr)轉(zhuǎn)為大寫lower(char_expr)轉(zhuǎn)為小寫space(int_expr)生成int_expr個空格replicate(char_expr,int_expr)復(fù)制字符串int_expr次reverse(char_expr)反轉(zhuǎn)

19、字符串stuff(char_expr1,start,length,char_expr2)將字符串char_expr1中的從start開始的length個字符用char_expr2代替ltrim(char_expr)rtrim(char_expr)取掉空格ascii(char)char(ascii)兩函數(shù)對應(yīng),取ascii碼,根據(jù)ascii嗎取字符字符串查找charindex(char_expr,expression)返回char_expr的起始位置patindex(%pattern%,expression)返回指定模式的起始位置,否則為02.數(shù)學(xué)函數(shù)abs(numeric_expr)求絕對值c

20、eiling(numeric_expr)取大于等于指定值的最小整數(shù)exp(float_expr)取指數(shù)floor(numeric_expr)小于等于指定值得最大整數(shù)pi()3.1415926.power(numeric_expr,power)返回power次方rand(int_expr)隨機數(shù)產(chǎn)生器round(numeric_expr,int_expr)安int_expr規(guī)定的精度四舍五入sign(int_expr)根據(jù)正數(shù),0,負數(shù),返回+1,0,-1sqrt(float_expr)平方根3.日期函數(shù)getdate()返回日期datename(datepart,date_expr)返回名稱如

21、Junedatepart(datepart,date_expr)取日期一部份datediff(datepart,date_expr1.dateexpr2)日期差dateadd(datepart,number,date_expr)返回日期加上number上述函數(shù)中datepart的寫法取值和意義yy1753-9999年份qq1-4刻mm1-12月dy1-366日dd1-31日wk1-54周dw1-7周幾hh0-23小時mi0-59分鐘ss0-59秒ms0-999毫秒日期轉(zhuǎn)換convert()4.系統(tǒng)函數(shù)suser_name()用戶登錄名user_name()用戶在數(shù)據(jù)庫中的名字user用戶在數(shù)據(jù)

22、庫中的名字show_role()對當(dāng)前用戶起作用的規(guī)則db_name()數(shù)據(jù)庫名object_name(obj_id)數(shù)據(jù)庫對象名col_name(obj_id,col_id)列名col_length(objname,colname)列長度valid_name(char_expr)是否是有效標(biāo)識符HYPERLINK/html/30/36/38/2010/07/30/112388.htmlSQLServer中的時間算法總結(jié)DECLAREDateDATETIMESETDate=GETDATE()-前一天，給定日期的前一天SELECTDATEADD(DAY,-1,Date)AS前一天-后一天，給定日

23、期的后一天SELECTDATEADD(DAY,1,Date)AS后一天GO-月初，計算給定日期所在月的第一天-這DECLAREDateDATETIMESETDate=GETDATE()-前一天，給定日期的前一天SELECTDATEADD(DAY,-1,Date)AS前一天-后一天，給定日期的后一天SELECTDATEADD(DAY,1,Date)AS后一天GO-月初，計算給定日期所在月的第一天-這個計算的技巧是先計算當(dāng)前日期到“1900-01-01”的時間間隔數(shù)，然后把它加到“1900-01-01”上來獲得特殊的日期，這個技巧可以用-來計算很多不同的日期。DECLAREDateDATETIME

24、SETDate=GETDATE()SELECTDATEADD(MONTH,DATEDIFF(MONTH,1900-01-01,Date),1900-01-01)AS所在月的第一天-精簡算法，根據(jù)SQLServer的時間表示方式可知，1900-01-01可以用0代替SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS所在月的第一天-上面兩種算法精確到天時分秒均為00:00:00.000-下面算法課以保留時分秒-思路：用給定日期減去月第一天與給定日期差的天數(shù)SELECTDATEADD(DAY,1-DATEPART(DAY,Date),Date)GO-月末，

25、計算給定日期所在月的最后一天DECLAREDateDATETIMESETDate=GETDATE()-思路：當(dāng)前月的下一月1號在減1天SELECTDATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,1900-01-01,Date),1900-01-01)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

26、,Date),0)AS所在月的最一天SELECTDATEADD(MONTH,1+DATEDIFF(MONTH,0,Date),0)-1AS所在月的最一天-思路：與月初計算思路相同SELECTDATEADD(MONTH,DATEDIFF(MONTH,1989-12-31,Date),1989-12-31)AS所在月的最一天-精簡算法，1989-12-31用-1代替SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,Date),-1)AS所在月的最一天-保留時分秒的算法SELECTDATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DA

27、TEPART(DAY,Date),Date)GO-其他月計算-計算給定日期所在月的上月第一天DECLAREDateDATETIMESETDate=GETDATE()-當(dāng)前月第一天減去一個月SELECTDATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS上月第一天-簡化SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,Date)-1,0)AS上月第一天-另一種當(dāng)前月第一天算法SELECTDATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)上月第一天GO

28、-計算給定日期所在月的上月最后一天DECLAREDateDATETIMESETDate=GETDATE()-當(dāng)前月第一天減去一天SELECTDATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS上月最后一天-另一種當(dāng)前月第一天算法SELECTDATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)上月最后一天SELECTDATEADD(DAY,1-DATEPART(DAY,Date),Date)-1上月最后一天-另一種算法，不能用當(dāng)前月的最后一天加一個月，因為當(dāng)前月可能是30天。-例如S

29、ELECTDATEADD(MONTH,1,2010-06-30)-結(jié)果是2010-07-30而不是2010-07-31，-這也是月末算法采用下月第一天減1天計算的原因-但是如果計算月是31天擇無此問題-例如SELECTDATEADD(MONTH,1,2010-05-31)-結(jié)果是2010-06-30-因此下面算法是正確的，-1表示1899-12-3100:00:00.000-SELECTCONVERT(DATETIME,-1)SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,Date)-1,-1)-另一種當(dāng)前月算法SELECTDATEADD(DAY,-1,DATEAD

30、D(DAY,1-DATEPART(DAY,Date),Date)上月最后一天-簡化SELECTDATEADD(DAY,0-DATEPART(DAY,Date),Date)上月最后一天GO-計算給定日期所在月的下月第一天DECLAREDateDATETIMESETDate=GETDATE()-當(dāng)前月第一天加一個月SELECTDATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0)AS下月第一天-簡化SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,Date)+1,0)AS下月第一天-另一種當(dāng)前月第一天算法SELECTD

31、ATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)下月第一天GO-計算給定日期所在月的下月最后一天DECLAREDateDATETIMESETDate=GETDATE()-當(dāng)前月第一天加2個月再減去1天SELECTDATEADD(DAY,-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(

32、MONTH,DATEDIFF(MONTH,0,Date)+2,0)-1AS下月最后一天-另一種算法SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,Date)+1,-1)下月最后一天-另一種當(dāng)前月第一天算法SELECTDATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,Date),Date)下月最后一天GO-所在星期的第一天，計算給定日期所在星期的第1天(星期日為第一天)DECLAREDateDATETIMESETDate=GETDATE()-與SQLServer語言版本相關(guān)的算法-思路：當(dāng)前日期+星期日

33、(每周的第1天)與當(dāng)前日期的差的天數(shù)-DATEPART(WEEKDAY,DATE)的返回值與DATEFIRST相關(guān)SETDATEFIRST7-或者設(shè)置為美國英語SETLANGUAGEus_english;(星期日為第一天)SELECTDATEADD(WEEKDAY,1-DATEPART(WEEKDAY,Date),Date)AS所在星期的第一天，星期日-星期日，與SQLServer語言版本或DATEFIRST無關(guān)-1989-12-31是星期日，1989-12-31再加上(當(dāng)前日期與1989-12-31差的星期數(shù))個星期SELECTDATEADD(WEEK,DATEDIFF(WEEK,-1,Da

34、te),-1)AS所在星期的星期日-或者SELECTDATEADD(WEEK,DATEDIFF(WEEK,6,Date),6)AS所在星期的星期日GO-所在星期的第二天，計算給定日期所在星期的第2天(星期日為第一天)DECLAREDateDATETIMESETDate=GETDATE()-思路：當(dāng)前日期+星期一(每周的第2天)與當(dāng)前日期的差的天數(shù)-DATEPART(WEEKDAY,DATE)的返回值與DATEFIRST相關(guān)SETDATEFIRST7-或者設(shè)置為美國英語SETLANGUAGEus_english;(星期日為第一天)SELECTDATEADD(DAY,2-DATEPART(WEEK

35、DAY,Date),Date)AS所在星期的第二天，星期一-星期一，與SQLServer語言版本或DATEFIRST無關(guān)-1900-01-01是星期一，1900-01-01再加上(當(dāng)前日期與1900-01-01差的星期數(shù))個星期SELECTDATEADD(WEEK,DATEDIFF(WEEK,0,Date),0)AS所在星期的星期一GO-上個星期第一天，計算給定日期所在星期的上一個星期日(星期日為第一天)DECLAREDateDATETIMESETDate=GETDATE()-思路：當(dāng)前日志所在星期的星期日再減1周-DATEPART(WEEKDAY,DATE)的返回值與DATEFIRST相關(guān)S

36、ETDATEFIRST7-或者設(shè)置為美國英語SETLANGUAGEus_english;(星期日為第一天)SELECTDATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS上個星期第一天，星期日-一周等于7天SELECTDATEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS上個星期第一天，星期日-簡化SELECTDATEADD(DAY,-6-DATEPART(WEEKDAY,Date),Date)AS上個星期第一天，星期日-上個星期日，與SQLServer語言版本或

37、DATEFIRST無關(guān)SELECTDATEADD(WEEK,-1+DATEDIFF(WEEK,-1,Date),-1)AS上個星期日-或者SELECTDATEADD(WEEK,DATEDIFF(WEEK,6,Date),-1)AS上個星期日GO-下個星期第一天，計算給定日期所在星期的下一個星期日(星期日為第一天)DECLAREDateDATETIMESETDate=GETDATE()-思路：當(dāng)前日志所在星期的星期日再加1周-DATEPART(WEEKDAY,DATE)的返回值與DATEFIRST相關(guān)SETDATEFIRST7-或者設(shè)置為美國英語SETLANGUAGEus_english;(星期

38、日為第一天)SELECTDATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS下個星期第一天，星期日-一周等于7天SELECTDATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date)AS下個星期第一天，星期日-簡化SELECTDATEADD(DAY,8-DATEPART(WEEKDAY,Date),Date)AS下個星期第一天，星期日-下個星期日，與SQLServer語言版本或DATEFIRST無關(guān)SELECTDATEADD(WEEK,1+DATEDIFF(WEEK,-1,Date),-1)AS下個星期日-或者SELECTDATEADD(WEEK,DATEDIFF(WEEK,-1,Date),6)AS下個星期日GO-判斷給定日期