SQL 函数

来源：互联网发布：parsley.js 表单验证编辑：程序博客网时间：2024/06/03 23:02

2.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
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

3.DATEDIFF和DATEADD组合

/*
convert(varchar(10),字段名,转换格式)
说明:
此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)
相互转换的时候才用到.
*/
/*
DATEDIFF(间隔参数,起始日期,末期日期)
间隔参数：year,month,week,day,quarter 等
*/
/*
DATEADD(添加参数,间隔时间段,所在日期添加)
DATEADD(YEAR,2,2008-01-01')
在2008-01-01日期的年份上添加2年
结果：2010-01-01
DATEADD(MM,3,2008-01-01)
在2008-01-01日期的月份上添加2月
结果：2008-03-01
*/
SELECT DATEDIFF(YEAR,0,GETDATE())--与1900-01-01日期相差的年份！
select dateadd(wk,DATEDIFF(wk,0,pmc要求完成日期),0) as 星期一 FROM MimiERP..v_report_orderbyall where PMC要求完成日期 is not null
select DATEDIFF(mm,0,getdate())--返回与0（1900-01-01）间隔多少月
Select DATEADD(mm, DATEDIFF(mm,0,'2009-03-5'),'1900-1-31') --返回当前月的最后一天
Select DATEADD(mm, DATEDIFF(mm,0,'2009-03-5'),'1900-1-1') --返回当前月的第一天
select DATEDIFF(WK,0,GETDATE())
select DATEADD(wk,DATEDIFF(WK,0,GETDATE()),0)--//返回当前日期当前所属周的第一天-monday
select DATEADD(wk,DATEDIFF(WK,0,GETDATE()),6)--返回当前日期当前所属周的周末-sunday
/*
一年的第一天

现在用年(yy)的时间间隔来显示这一年的第一天。
*/
　　Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
/*
　　季度的第一天

　　假如你要计算这个季度的第一天，这个例子告诉你该如何做。
*/

　　Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
/*
　　当天的半夜

　　曾经需要通过getdate()函数为了返回时间值截掉时间部分，就会考虑到当前日期是不是在半夜。假如这样，这个例子使用DATEDIFF和DATEADD函数来获得半夜的时间点。
　*/　
Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) --返回凌晨

Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) ----返回季度的第一天

Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) --返回上年的最后一天，利用减3MS毫秒获得
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()),-1)--返回上年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))--获取本年的最后一天,利用减3MS毫秒获得
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate())+1,-1)--返回上年的最后一天,获取明年的头一天，再减一天获得

4.SQL Server DATEDIFF() 函数

SQL Server Date 函数

定义和用法

DATEDIFF() 函数返回两个日期之间的天数。

语法

DATEDIFF(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。

datepart 参数可以是下列的值：

datepart缩写年yy, yyyy季度qq, q月mm, m年中的日dy, y日dd, d周wk, ww星期dw, w小时hh分钟mi, n秒ss, s毫秒ms微妙mcs纳秒ns

实例

例子 1

使用如下 SELECT 语句：

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

结果：

DiffDate1

例子 2

使用如下 SELECT 语句：

SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDate

结果：

DiffDate-1

5.SQL Server DATEADD() 函数

SQL Server Date 函数

定义和用法

DATEADD() 函数在日期中添加或减去指定的时间间隔。

语法

DATEADD(datepart,number,date)

date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

datepart 参数可以是下列的值：

datepart缩写年yy, yyyy季度qq, q月mm, m年中的日dy, y日dd, d周wk, ww星期dw, w小时hh分钟mi, n秒ss, s毫秒ms微妙mcs纳秒ns

实例

假设我们有下面这个 "Orders" 表：

OrderIdProductNameOrderDate1'Computer'2008-12-29 16:25:46.635

现在，我们希望向 "OrderDate" 添加 2 天，这样就可以找到付款日期。

我们使用如下 SELECT 语句：

SELECT OrderId,DATEADD(day,2,OrderDate) AS OrderPayDateFROM Orders

结果：

OrderIdOrderPayDate12008-12-31 16:25:46.635

6.ADD_MONTHS

ADD_MONTHS(DATE,NUMBER) 即ADD_MONTHS(日期，数字）

ADD_MONTHS函数在输入日期上加上指定的几个月返回一个新的日期。如果给出一负数，返回值日期之前几个月日期。

ADD_MONTHS(DATE,NUMBER)中的NUMBER应当是整数，给出小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。

例：add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99) 返回 28－Feb-95

注：上例中29调整为28，是因为96年二月份最后一天是29号，而95年二月份最后一天是28号。

add_months(to_date('15-Nov-1961','d-mon-yyyy'),1) 返回 15-Dec-1961

add_months(to_date('30-Nov-1961','d-mon-yyyy'),1) 返回 31-Dec-1961

注：从30调整为31，为了保持都是对应最后一天。

add_months(to_date('31-Jan-1999','d-mon-yyyy'),1) 返回 28-Feb-1999

注：函数将31日调为28日，以使结果对应新一月的最后一天，因1999年2月只有28天。

7.months_between

select months_between(trunc(to_date('2013-08-01','yyyy-mm-dd'),'mm'),trunc(to_date('2013-07-21','yyyy-mm-dd'),'mm')) from dual;

8.SQL TOP 子句

TOP 子句

TOP 子句用于规定要返回的记录的数目。

对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。

注释：并非所有的数据库系统都支持 TOP 子句。

SQL Server 的语法：

SELECT TOP number|percent column_name(s)FROM table_name

MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的

MySQL 语法

SELECT column_name(s)FROM table_nameLIMIT number

例子

SELECT *FROM PersonsLIMIT 5

Oracle 语法

SELECT column_name(s)FROM table_nameWHERE ROWNUM <= number

例子

SELECT *FROM PersonsWHERE ROWNUM <= 5

原始的表 (用在例子中的)：

Persons 表:

IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing4ObamaBarackPennsylvania AvenueWashington

SQL TOP 实例

现在，我们希望从上面的 "Persons" 表中选取头两条记录。

我们可以使用下面的 SELECT 语句：

SELECT TOP 2 * FROM Persons

结果：

IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York

SQL TOP PERCENT 实例

现在，我们希望从上面的 "Persons" 表中选取 50% 的记录。

我们可以使用下面的 SELECT 语句：

SELECT TOP 50 PERCENT * FROM Persons

结果：

IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York

9.extract

//Oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分
//语法如下：
EXTRACT (
        { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
        | { TIMEZONE_HOUR | TIMEZONE_MINUTE }
        | { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);
//我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
select extract(year from date'2011-05-17') year from dual;
      YEAR
----------
      2011
select extract(month from date'2011-05-17') month from dual;
     MONTH
----------
         5
select extract(day from date'2011-05-17') day from dual;
       DAY
----------
        17
//获取两个日期之间的具体时间间隔，extract函数是最好的选择
select extract(day from dt2-dt1) day
      ,extract(hour from dt2-dt1) hour
      ,extract(minute from dt2-dt1) minute
      ,extract(second from dt2-dt1) second
from (
     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1
           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
     from dual)
/
       DAY       HOUR     MINUTE     SECOND
---------- ---------- ---------- ----------
       102          4          1         46
--

2、ORACLE里获取一个时间的年、季、月、周、日的函数

select to_char(sysdate, 'yyyy' ) from dual; --年

select to_char(sysdate, 'MM' ) from dual; --月

select to_char(sysdate, 'dd' ) from dual; --日

select to_char(sysdate,'Q') from dual; --季

select to_char(sysdate,'iw') from dual; --周--按日历上的那种，每年有52或者53周

10.sqrt、power

Oracle 计算平方根和立方根的算法
平方的有 sqrt,pow 函数（double pow(double x, double y);）用来计算 x 的 y 次幂。

立方根计算可以用 pow 函数实现。如计算 x 的立方根：pow( x, 1.0/3.0 )
例如:
select sqrt(4),power(8,1/3) from dual;

0 0