Sybase常见日期函数使用说明

类型 取值范围
date

January 1, 1753 through December 31,9999

time

January 1, 1900 through June 6, 2079

smalldatetime

January 1, 0001 to December 31, 9999

datetime

12:00:00.000000AM to 11:59:59.999999PM

bigdatetime

January 1, 0001 to December 31, 9999

bigtime

12:00:00 AM to 11:59:59:990 PM

函数  
类型转换函数 功能描述
cast 类型强转
select cast("01/03/63" as datetime)
go

 --------------------------
        Jan  3 1963 12:00AM

(1 row affected)
convert 类型强转,显示格式转换
convert (datatype [(length) | (precision[, scale])] 
	[null | not null], expression [, style])
日期函数 功能描述
current_bigdatetime
select current_bigdatetime())
------------------------------
Nov 25 1995 10:32:00.010101AM
current_bigtime
select current_bigtime())
------------------------------
10:32:00.010101AM

select datepart(us, current_bigtime())
------------------------------
01010
current_date
1> select datename(month, current_date())
2> go

------------------------------ 
August 


1> select datepart(month, current_date())
2> go

----------- 
8 
current_time
1> select current_time()
2> go

------------------------ 
            12:29PM


1> select datename(minute, current_time())
2> go

------------------------------ 
45

(1 row affected)
dateadd
dateadd(date_part, integer, {date | time | bigtime | datetime, | bigdatetime})

declare @a date
select @a = "apr 12, 9999"
select dateadd(dd, 1, @a)
--------------------------
Apr 13 9999



select dateadd(mi, -5, convert(time, "14:20:00"))
--------------------------
2:15PM



declare @a datetime
select @a = "apr 12, 2013 14:20:00 "
select dateadd(hh, 25, @a)
-------------------------- 
Apr 13 2013 3:20PM 


datediff
datediff(datepart, {date, date | time, time | bigtime, bigtime | datetime, datetime | bigdatetime, bigdatetime}])

declare @a date
declare @b date
select @a = "apr 1, 1999"
select @b = "apr 2, 1999"
select datediff(hh, @a, @b)
-----------
     24
datename
datename(datepart {date | time | bigtime | datetime | bigdatetime})

declare @a bigdatetime
select @a = "apr 12, 0001 00:00:00.010101"
select datename(mm, @a)
------------------------------
April
datepart
datepart(date_part {date | time | datetime | bigtime | bigdatetime}))

select datepart(year, pubdate) from titles 
    where type = "trad_cook"

 -----------
        1990 
        1985 
        1987 

declare @a time
select @a = "20:43:22"
select datepart(hh, @a)
-----------
    20
getdate
select getdate()

Nov 25 1995 10:32AM
getutcdate UTC值
isdate
select isdate(stor_id), isdate(date) from sales

---- ---- 
0    1 
日期快捷取值函数 功能描述
year year(date_expression)等价于datepart(yy, date_expression).
month month(date_expression)等价于datepart(mm, date_expression).
day day(date_expression)等价于datepart(dd,date_expression)
条件函数 功能描述
isnull isnull(expression1, expression2),表达式expression1为NULL时使用expression2,相当于Oracle中的NVL
coalesce coalesce(expression, expression [, expression]...),返回第一个值不为NULL的表达式,如果所有表达式都为NULL,结果返回NULL
case
select stor_id, discount,
        case
            when lowqty is not NULL then lowqty
            else highqty
        end
from discounts
nullif
select title,
    nullif(type, "UNDECIDED")
from titles
等价于
select title, 
    case
        when type = "UNDECIDED" then NULL
        else type
    end
from titles

参考文档:

类型参考:http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36271.1570/html/blocks/X58954.htm

函数参考:http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36271.1570/html/blocks/CHDIHGDF.htm

Sybase常见日期函数使用说明,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。