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