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