SQL必備-ORACLE-SQSLSERVER-DB2時間函數及常見函數總結

標籤：sql oracle sqlserver db2 時間函數

SQLSERVER 時間篇：*************************************************************
一、時間函數
 --getdate 擷取目前時間
 select getdate()

 --dateadd 原有時間加： 2013-02-17 13:20:16 此時間加12個月
 select dateadd(MONTH,12,‘2013-02-17 13:20:16‘)  --返回：2014-02-17 13:20:16.000  （參數month可以改為 day，year等日期加相應的值）

 --datediff 兩個時間的差 （後面-前面=傳回值）
 select datediff(day,‘2013-02-01‘,‘2013-02-18‘)   --返回：17  （參數day可以改為 month，year等日期加相應的值）
 
 --datepart 擷取日期的某個部分整數
 select DATEPART(month, ‘2013-2-17‘)  --返回 2    （參數month可以改為 day，year等日期加相應的值）
 
 --datename 擷取指定部位的字串
 select datename(weekday, ‘2013-2-17‘)  --返回 星期日 （參數weekday可以改為 day，year等日期加相應的值）

 --day(), month(),year() 擷取指定部位的字串
 select day(‘2013-2-15‘) --返回15

二、時間格式轉換

    select CONVERT(varchar, getdate(), 120 )  --返回 2013-02-17 13:37:54
    select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),‘-‘,‘‘),‘ ‘,‘‘),‘:‘,‘‘) --返回 20130217133828
    select CONVERT(varchar(12) , getdate(), 111 )  --返回 2013/02/17
    select CONVERT(varchar(12) , getdate(), 112 )  --返回 20130217
    select CONVERT(varchar(12) , getdate(), 102 )  --返回 2013.02.17
    select CONVERT(varchar(12) , getdate(), 101 )  --返回 02/17/2013
    select CONVERT(varchar(12) , getdate(), 103 )  --返回 17/02/2013
    select CONVERT(varchar(12) , getdate(), 104 )  --返回 17.02.2013
    select CONVERT(varchar(12) , getdate(), 105 )  --返回 17-02-2013
    select CONVERT(varchar(12) , getdate(), 106 )  --返回 17 02 2013
    select CONVERT(varchar(12) , getdate(), 107 )  --返回 02 17, 2013
    select CONVERT(varchar(12) , getdate(), 108 )  --返回 13:42:50
    select CONVERT(varchar(12) , getdate(), 109 )  --返回 02 17 2013  
    select CONVERT(varchar(12) , getdate(), 110 )  --返回 02-17-2013
    select CONVERT(varchar(12) , getdate(), 113 )  --返回 17 02 2013 1
    select CONVERT(varchar(12) , getdate(), 114 )  --返回 13:42:24:743

三、時間格式與其他格式轉換
   
   將int轉換為時間：convert(datetime,left(DATEid,8),101)
   將時間轉換為int: year(CreateDate)*10000 + month(CreateDate)*100 + day(CreateDate)
   將時間格式[2014-07-24 10:32:43.197]轉換為int20140724：select cast(replace(convert(char(10),GETDATE(),120),‘-‘,‘‘) as int)

SQLSERVER 函數篇： *************************************************************

一、字元函數
ascii(字串運算式)
char(整數運算式)
charindex(字串運算式 1, 字串運算式2[,整數運算式])
difference(字串運算式 1，字串運算式 2)
left(字串運算式，整數運算式)
right(字串運算式，整數運算式)
len(字串運算式)
lower(字串運算式)
upper(字串運算式)
ltrim(字串運算式)
rtrim(字串運算式)
patindex(字串運算式 1，字串運算式 2)
reverse(字串運算式)
space(整數運算式)
str（float 型小數[,總長度[,小數點後保留的位元]]）
stuff (字串運算式 1，開始位置，長度，字串運算式 2)
substring(字串運算式，開始位置，長度)
replace(字串運算式 1，字串運算式 2，字串運算式 3)
二、數學函數
abs(數值運算式)
cos(浮點運算式)
sin(浮點運算式)
cot(浮點運算式)
ceiling(數值運算式)
floor(數值運算式)
rand([整數運算式])
round(數值運算式[,長度[,操作方式]])
三、日期函數
dateadd(日期部分，數字，日期)
datediff(日期部分，開始日期，結束日期)
datename(日期部分，日期)
datepart(日期部分，日期)
day(日期) month (日期) year (日期)
四、轉換函式
convert(資料類型[(長度)]，運算式[，樣式]）
cast(運算式 as 資料類型[(長度)])
getdate()
五、系統和功能函數
select newid()
isnumeric(任意運算式)
isdate（任意運算式）  
----------------------------------------------------------------------------
ORACLE 時間篇：*************************************************************

一、目前時間拆分：
select to_char(sysdate,‘yyyy‘) as nowYear   from dual;   //擷取時間的年  
select to_char(sysdate,‘mm‘)    as nowMonth from dual;   //擷取時間的月  
select to_char(sysdate,‘dd‘)    as nowDay    from dual;   //擷取時間的日
select to_char(sysdate,‘day‘) from dual;  
select to_char(sysdate,‘hh24‘) as nowHour   from dual;   //擷取時間的時  
select to_char(sysdate,‘mi‘)    as nowMinute from dual;   //擷取時間的分  
select to_char(sysdate,‘ss‘)    as nowSecond from dual;   //擷取時間的秒
select to_date(‘2004-05-07 13:23:44‘,‘yyyy-mm-dd hh24:mi:ss‘)    from dual

二、目前時間自身計算：
目前時間減去 7 分鐘的時間
select sysdate,sysdate - interval ‘7‘ MINUTE from dual
目前時間減去 7 小時的時間
select sysdate - interval ‘7‘ hour from dual
目前時間減去 7 天的時間
select sysdate - interval ‘7‘ day from dual
目前時間減去 7 月的時間
select sysdate,sysdate - interval ‘7‘ month from dual
目前時間減去 7 年的時間
select sysdate,sysdate - interval ‘7‘ year from dual  

三、目前時間其他計算：
START_DATE，END_DATE，計算這兩個日期的時間差（分別以天，小時，分鐘，秒，毫秒）：
天：   ROUND(TO_NUMBER(sysdate - START_DATE))
小時： ROUND(TO_NUMBER(sysdate - START_DATE) * 24)
分鐘： ROUND(TO_NUMBER(sysdate - START_DATE) * 24 * 60)
秒：   ROUND(TO_NUMBER(sysdate - START_DATE) * 24 * 60 * 60)
毫秒： ROUND(TO_NUMBER(sysdate - START_DATE) * 24 * 60 * 60 * 1000)

四、目前時間轉換

時間轉換為char:
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) as nowTime from dual;    
char轉換為時間:
select to_date(‘2003-10-17 21:15:37‘,‘yyyy-mm-dd hh24:mi:ss‘) from dual


ORACLE 函數篇： *************************************************************

一、字元函數
lower(char)：將字串轉化為小寫格式。
upper(char)：將字串轉化為大寫的格式。
length(char)：返回字串的長度。
substr(char, m, n)：截取字串的子串，n代表取n個字元的意思，不是代表取到第n個
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判斷某字元或字串是否存在，存在返回出現的位置的索引，否則返回小於1;在一個字串中搜尋指定的字元,返回傳現指定的字元的位置;
C1 被搜尋的字串
C2 希望搜尋的字串
I 搜尋的開始位置,預設為1
J 出現的位置,預設為1

二、數學函數
round(n,[m]) 該函數用於執行四捨五入
trunc(n,[m]) 該函數用於截取數字。
mod(m,n)取餘函數
floor(n) 返回小於或是等於n的最大整數
ceil(n) 返回大於或是等於n的最小整數
abs(n) 返回數字n的絕對值

三、日期函數
sysdate 返回系統時間
add_months函數 可以得到某一時間之前或之後n個月的時間
last_day(d) 返回指定日期所在月份的最後一天

四、轉換函式
to_date()函數
to_char()函數

五、系統和功能函數
1)terminal：當前會話客戶所對應的終端的標示符，如電腦名稱
2)language: 語言
3)db_name： 當前資料庫名稱
4)nls_date_format： 當前會話客戶所對應的日期格式
5)session_user： 當前會話客戶所對應的資料庫使用者名稱
6)current_schema： 當前會話客戶所對應的預設方案名
7)host： 返回資料庫所在主機的名稱



Oracle trunc()函數的用法
/**************日期********************/
1.select trunc(sysdate) from dual  --2011-3-18  今天的日期為2011-3-18
2.select trunc(sysdate, ‘mm‘)   from   dual  --2011-3-1    返回當月第一天.
3.select trunc(sysdate,‘yy‘) from dual  --2011-1-1       返回當年第一天
4.select trunc(sysdate,‘dd‘) from dual  --2011-3-18    返回當前年月日
5.select trunc(sysdate,‘yyyy‘) from dual  --2011-1-1   返回當年第一天
6.select trunc(sysdate,‘d‘) from dual  --2011-3-13 (星期天)返回當前星期的第一天
7.select trunc(sysdate, ‘hh‘) from dual   --2011-3-18 14:00:00   目前時間為14:41  
8.select trunc(sysdate, ‘mi‘) from dual  --2011-3-18 14:41:00   TRUNC()函數沒有秒的精確
/***************數字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的數字。
Num_digits 用於指定取整精度的數字。Num_digits 的預設值為 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 --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.458
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
-------------------------------------------------

DB2：

 --擷取當前日期： select current date from sysibm.sysdummy1;  
                  values current date;   
 --擷取當前日期   select current time from sysibm.sysdummy1;
                  values current time;  
 --擷取目前時間戳 select current timestamp from sysibm.sysdummy1;
                  values current timestamp;   
 --要使目前時間或目前時間戳記調整到 GMT/CUT，則把當前的時間或時間戳記減去當前時區寄存器：
         values current time -current timezone;  
         values current timestamp -current timezone;   
--擷取當前年份   values year(current timestamp);
--擷取當前月     values month(current timestamp);
--擷取當前日     values day(current timestamp);  
--擷取當前時     values hour(current timestamp);
--擷取分鐘       values minute(current timestamp);   
--擷取秒        values second(current timestamp);  
--擷取毫秒      values microsecond(current timestamp);  
--從時間戳記單獨抽取出日期和時間  
  values date(current timestamp); 07/24/14
  values VARCHAR_FORMAT(current TIMESTAMP,‘yyyy-mm-dd‘);  
  values char(current date);  2014-07-24-10.46.51.978540
  values time(current timestamp);  10:47:25
--執行日期和時間的計算
  values current date+1 year;  
  values current date+3 years+2 months +15 days;
  values current time +5 hours -3 minutes +10 seconds;   
--計算兩個日期之間的天數   
  values days(current date)- days(date(‘2010-02-20‘));    
--時間和日期換成字串   
  values char(current date);   
  values char(current time);
--要將字串轉換成日期或時間值   
  values timestamp(‘2010-03-09-22.43.00.000000‘);
  values timestamp(‘2010-03-09 22:44:36‘);   
  values date(‘2010-03-09‘);
  values date(‘03/09/2010‘);   
  values time(‘22:45:27‘);   
  values time(‘22.45.27‘);  
  --計算兩個時間戳記之間的時差：   
  --秒的小數部分為單位   
  values timestampdiff(1,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --秒為單位
  values timestampdiff(2,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --分為單位
  values timestampdiff(4,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --小時為單位
  values timestampdiff(8,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --天為單位  
  values timestampdiff(16,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --周為單位   
  values timestampdiff(32,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --月為單位   
  values timestampdiff(64,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --季度為單位   
  values timestampdiff(128,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));   --年為單位   
  values timestampdiff(256,char(current timestamp - timestamp(‘2010-01-01-00.00.00‘)));

  將時間轉換為int:CAST(TO_CHAR(IN_DATE,‘YYYYMMDD‘) AS INT) AS DATEID

本文出自 “獨垂淚” 部落格，謝絕轉載！

SQL必備-ORACLE-SQSLSERVER-DB2時間函數及常見函數總結