SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数：

函数 描述
GETDATE() 返回当前日期和时间
DATEPART() 返回日期/时间的单独部分
DATEADD() 在日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期之间的时间
CONVERT() 用不同的格式显示日期/时间

GETDATE() 函数

定义和用法

GETDATE() 函数从 SQL Server 返回当前的时间和日期。

语法

1
GETDATE()

实例

例子 1

使用下面的 SELECT 语句：

1
SELECT GETDATE() AS CurrentDateTime

结果：

CurrentDateTime
2008-12-29 16:25:46.635

注释：上面的时间部分精确到毫秒。

例子 2

下面的 SQL 创建带有日期时间列 (OrderDate) 的 “Orders” 表：

1
2
3
4
5
6
CREATE TABLE Orders 
(
OrderId int NOT NULL PRIMARY KEY,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT GETDATE()
)

请注意，OrderDate 把 GETDATE() 规定为默认值。结果是，当您在表中插入新行时，当前日期和时间自动插入列中。

现在，我们希望在 “Orders” 表中插入一条记录：

1
INSERT INTO Orders (ProductName) VALUES ('Computer')

“Orders” 表将成为这样：

OrderId ProductName OrderDate
1 ‘Computer’ 2008-12-29 16:25:46.635

DATEPART() 函数

定义和用法

DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法

1
DATEPART(datepart,date)

date 参数是合法的日期表达式。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” 表：

OrderId ProductName OrderDate
1 ‘Computer’ 2008-12-29 16:25:46.635

我们使用如下 SELECT 语句：

1
2
3
4
5
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1

结果：

OrderYear OrderMonth OrderDay
2008 12 29

DATEADD() 函数

定义和用法

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

语法

1
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” 表：

OrderId ProductName OrderDate
1 ‘Computer’ 2008-12-29 16:25:46.635

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

我们使用如下 SELECT 语句：

1
2
SELECT OrderId,DATEADD(day,2,OrderDate) AS OrderPayDate
FROM Orders

结果：

OrderId OrderPayDate
1 2008-12-31 16:25:46.635

DATEDIFF() 函数

定义和用法

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

语法

1
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 语句：

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

结果：

DiffDate
1
例子 2

使用如下 SELECT 语句：

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

结果：

DiffDate
-1

CONVERT() 函数

定义和用法

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。

语法

1
CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

可以使用的 style 值：

格式编号 示例查询 示例结果
0 SELECT CONVERT(NVARCHAR, GETDATE(), 0) Aug 23 2019 1:39PM
1 SELECT CONVERT(NVARCHAR, GETDATE(), 1) 08/23/19
2 SELECT CONVERT(NVARCHAR, GETDATE(), 2) 19.08.23
3 SELECT CONVERT(NVARCHAR, GETDATE(), 3) 23/08/19
4 SELECT CONVERT(NVARCHAR, GETDATE(), 4) 23.08.19
5 SELECT CONVERT(NVARCHAR, GETDATE(), 5) 23-08-19
6 SELECT CONVERT(NVARCHAR, GETDATE(), 6) 23 Aug 19
7 SELECT CONVERT(NVARCHAR, GETDATE(), 7) Aug 23, 19
8、24 或 108 SELECT CONVERT(NVARCHAR, GETDATE(), 8) 13:39:17
9 或 109 SELECT CONVERT(NVARCHAR, GETDATE(), 9) Aug 23 2019 1:39:17:090PM
10 SELECT CONVERT(NVARCHAR, GETDATE(), 10) 08-23-19
11 SELECT CONVERT(NVARCHAR, GETDATE(), 11) 19/08/23
12 SELECT CONVERT(NVARCHAR, GETDATE(), 12) 190823
13 或 113 SELECT CONVERT(NVARCHAR, GETDATE(), 13) 23 Aug 2019 13:39:17:090
14 或 114 SELECT CONVERT(NVARCHAR, GETDATE(), 14) 13:39:17:090
20 或 120 SELECT CONVERT(NVARCHAR, GETDATE(), 20) 2019-08-23 13:39:17
21、25 或 121 SELECT CONVERT(NVARCHAR, GETDATE(), 21) 2019-08-23 13:39:17.090
22 SELECT CONVERT(NVARCHAR, GETDATE(), 22) 08/23/19 1:39:17 PM
23 SELECT CONVERT(NVARCHAR, GETDATE(), 23) 2019-08-23
101 SELECT CONVERT(NVARCHAR, GETDATE(), 101) 08/23/2019
102 SELECT CONVERT(NVARCHAR, GETDATE(), 102) 2019.08.23
103 SELECT CONVERT(NVARCHAR, GETDATE(), 103) 23/08/2019
104 SELECT CONVERT(NVARCHAR, GETDATE(), 104) 23.08.2019
105 SELECT CONVERT(NVARCHAR, GETDATE(), 105) 23-08-2019
106 SELECT CONVERT(NVARCHAR, GETDATE(), 106) 23 Aug 2019
107 SELECT CONVERT(NVARCHAR, GETDATE(), 107) Aug 23, 2019
110 SELECT CONVERT(NVARCHAR, GETDATE(), 110) 08-23-2019
111 SELECT CONVERT(NVARCHAR, GETDATE(), 111) 2019/08/23
112 SELECT CONVERT(NVARCHAR, GETDATE(), 112) 20190823
113 SELECT CONVERT(NVARCHAR, GETDATE(), 113) 23 Aug 2019 13:39:17.090
120 SELECT CONVERT(NVARCHAR, GETDATE(), 120) 2019-08-23 13:39:17
121 SELECT CONVERT(NVARCHAR, GETDATE(), 121) 2019-08-23 13:39:17.090
126 SELECT CONVERT(NVARCHAR, GETDATE(), 126) 2019-08-23T13:39:17.090
127 SELECT CONVERT(NVARCHAR, GETDATE(), 127) 2019-08-23T13:39:17.090
130 SELECT CONVERT(NVARCHAR, GETDATE(), 130) 22 ذو الحجة 1440 1:39:17.090P
131 SELECT CONVERT(NVARCHAR, GETDATE(), 131) 22/12/1440 1:39:17.090PM

实例

下面的脚本使用 CONVERT() 函数来显示不同的格式。我们将使用 GETDATE() 函数来获得当前的日期/时间：

1
2
3
4
CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),110) 
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

结果类似：

1
2
3
4
Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

  • DATE - 格式 YYYY-MM-DD
  • DATETIME - 格式: YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式: 唯一的数字

实例

根据”填报类型”字段的值选择不同的时间字段

convert(varchar,(CASE new_type WHEN '1' THEN DATEADD(HOUR, 8, new_actualfinishtime) WHEN '2' THEN DATEADD(HOUR, 8, new_submitdate) END),23 )submittime

1.当new_type 值为1时取时间为 new_actualfinishtime字段的值,当new_type 值为2时取时间为new_submitdate字段的值.

(CASE new_type WHEN ‘1’ THEN new_actualfinishtime WHEN ‘2’ THEN new_submitdate END) 时间

2.现在因为数据库中的时间为0时区,所以转换为北京时间要加上8小时,所以为

(CASE new_type WHEN ‘1’ THEN DATEADD(HOUR, 8, new_actualfinishtime) WHEN ‘2’ THEN DATEADD(HOUR, 8, new_submitdate) END) 时间

3.然后又需要将时间转换为日期格式,

加过之后为

convert(varchar,(CASE new_type WHEN ‘1’ THEN DATEADD(HOUR, 8, new_actualfinishtime) WHEN ‘2’ THEN DATEADD(HOUR, 8, new_submitdate) END),23 ) 时间

查指定时间范围内的数据

因为时间在两个字段内,所以可以使用

(( new_actualfinishtime BETWEEN ‘${START_TIME}’ and’${FINISH_TIME}’) or ( new_submitdate BETWEEN ‘${START_TIME}’ and’${FINISH_TIME}’) )

其中’${START_TIME}’和’${FINISH_TIME}’为帆软报表内选择的时间,格式为其固定的格式

但是又想只选年月就查到当月的数据 ,所以使用

DateDiff (interval,date1,date2)函数

以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1

interval 格式使用Year和Month,差值都是0

(((DateDiff(month,new_actualfinishtime, ‘${START_TIME}’) = 0 ) and (DateDiff(year,new_actualfinishtime, ‘${START_TIME}’ ) = 0))or( (DateDiff(month,new_submitdate, ‘${START_TIME}’) = 0 ) and (DateDiff(year,new_submitdate, ‘${START_TIME}’ ) = 0)))

同时时间又因为时区问题需要加上8小时,所以再使用

DateAdd (interval,number,date)函数

以interval指定的方式，加上number之后的日期

最后为

(((DateDiff(month,DATEADD(HOUR, 8, new_actualfinishtime), ‘${START_TIME}’) = 0 ) and (DateDiff(year,DATEADD(HOUR, 8, new_actualfinishtime), ‘${START_TIME}’ ) = 0))or( (DateDiff(month,DATEADD(HOUR, 8, new_submitdate), ‘${START_TIME}’) = 0 ) and (DateDiff(year,DATEADD(HOUR, 8, new_submitdate), ‘${START_TIME}’ ) = 0)))

最终的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT 
 convert(varchar,(CASE new_type WHEN '1' THEN DATEADD(HOUR, 8, new_actualfinishtime) WHEN '2' THEN DATEADD(HOUR, 8, new_submitdate) END),23 )submittime,
 new_ord_sales_idName 业务员,
 (CASE new_type WHEN '1' THEN '客户拜访' WHEN '2' THEN '工作日志' END)填报类型,
 location 工作地点,
 new_jobcontent 工作内容,
 new_account_idName 客户名称,
 new_telphone 客户电话,
 new_entertainfee 招待费,
 new_accommodationfee 住宿费,
 new_trafficfee 交通费,
 new_miscellaneousfee 公杂费,
 new_other 其他费用,
 new_dealers_idName 部门
FROM
 appointment
 WHERE 1=1
 and new_ord_sales_id='${id}'
--and(( new_actualfinishtime BETWEEN '${START_TIME}' and'${FINISH_TIME}') 
--or ( new_submitdate BETWEEN '${START_TIME}' and'${FINISH_TIME}') ) 
and(((DateDiff(month,DATEADD(HOUR, 8, new_actualfinishtime), '${START_TIME}') = 0 ) 
 and (DateDiff(year,DATEADD(HOUR, 8, new_actualfinishtime), '${START_TIME}' ) = 0))
or( (DateDiff(month,DATEADD(HOUR, 8, new_submitdate), '${START_TIME}') = 0 ) and (DateDiff(year,DATEADD(HOUR, 8, new_submitdate), '${START_TIME}' ) = 0)))
order by submittime

SQLserver数据库函数参数格式说明

函数 参数/功能
GetDate( ) 返回系统目前的日期与时间
DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期
DatePart (interval,date) 返回日期date中，interval指定部分所对应的整数值
DateName (interval,date) 返回日期date中，interval指定部分所对应的字符串名称

interval的可选值

值 缩 写（Sql Server） Access 和 ASP 说明
Year Yy yyyy 年 1753 ~ 9999
Quarter Qq q 季 1 ~ 4
Month Mm m 月1 ~ 12
Day of year Dy y 一年的日数,一年中的第几日 1-366
Day Dd d 日，1-31
Weekday Dw w 一周的日数，一周中的第几日 1-7
Week Wk ww 周，一年中的第几周 0 ~ 51
Hour Hh h 时0 ~ 23
Minute Mi n 分钟0 ~ 59
Second Ss s 秒 0 ~ 59
Millisecond Ms - 毫秒 0 ~ 999