oracle 與sql server 部分內建函數替換

標籤：

1，trunc--convert

 oracle: select trunc(sysdate) from dual   輸出：2016/5/24

 sql server:select CONVERT(varchar(100), GETDATE(), 111) 輸出：2016/05/24

2，to_char--datename

oracle:select to_char(sysdate,‘day‘)  from dual; 輸出星期二

sql server:Select datename(weekday, getdate());輸出星期二

3 ROWNUM--SQL server行號

oracle:select ROWNUM from dual

sql server:select row_number()over(order by userid )as ROWNUM,*from Table

4 trunc--Convert--ROUND

oracle:select trunc(123.4586743,5) from dual 輸出：123.45867

sql server:select  Convert(decimal(18,5),123.4586743) 輸出123.45867

sql server：select ROUND(123.4586743,5)輸出123.4586700

5 取三十天的時間

oracle:

SELECT (TRUNC(sysdate - 30) + (ROWNUM - 1)) as rq
FROM dual
CONNECT BY ROWNUM <= TRUNC(sysdate) - TRUNC(sysdate-30) + 1;

sql server:

select CONVERT(varchar(100), dateadd(dd,number,getdate()-30), 111) AS date
from master..spt_values
where type=‘p‘ and dateadd(dd,number,getdate()-30)<=getdate()

輸出

oracle 與sql server 部分內建函數替換