类型 | 取值范围 |
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
|