日期时间
时间日期类型
- date 日期格式的类型，默认的字面量格式 YYYY-MM-DD。
INSERT INTO account values(12,'zhx','1997-10-1')
- datetime 带有日期和时间，时间格式的字符串自动转换为datetime。
INSERT INTO account values(12,'zhx','1997-10-1 12:23:34')
- datetime2 对datetime的拓展，有着更大的范围和更大的精度和用户可以指定的精度。默认的字面量格式为：YYYY-MM-DD hh:mm:ss[.fractional seconds] 。
INSERT INTO account(birthday) values('2017-09-01 22:22:10')
- datetimeoffset 由日期时间加上偏移量组成。时区偏移量指定某个 time 或 datetime 值相对于 UTC 的时区偏移量。时区偏移量可以表示为 [+|-] hh:mm。hh 是两位数，范围为 00 到 14，表示时区偏移量中的小时数。mm 是两位数，范围为 00 到 59，表示时区偏移量中的额外分钟数。默认的字面量格式为：YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]。
INSERT INTO account(birthday) values('2017-08-09 12:23:34 +12:12')
- time 定义一天中的某个时间。此时间不能感知时区且基于 24 小时制。默认字面量格式为：hh:mm:ss[.nnnnnnn]。
INSERT INTO account(eattime) values('23:12:34');
日期函数
- 获取当前日期和时间：GETDATE()，返回内型datetime。
- 获取年份：YEAR(date)，返回日期对应年份，返回int类型。date是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。date 参数可以是表达式、列表达式、用户定义变量或字符串文字。
- 获取月份：MONTH(date),返回日期对应的月份，返回int类型。参数同上。
- 获取天数：DAY(date),返回日期对应的天数，返回int类型。参数同上
- 获取任意时间部分，DATEPART ( datepart , date )，返回int类型。date同上，datepart解析如下。
| 说明 | 取值 |
|---|---|
| 返回年度信息 | Year、YYYY、YY |
| 返回月份信息 | Month、MM、M |
| 返回日期信息 | Day、DD、D |
| 返回周信息 | Week、WK、WW |
| 返回每周星期几信息，返回值取决于使用SET DATEFIRST设置的值。在我的电脑上测试周日的值为1 | WeekDay、DW |
| 返回季度信息 | Quarter、QQ、Q |
| 返回一年中第几天的信息 | DayOfYear、DY、Y |
| 返回小时信息 | Hour、HH |
| 返回分钟信息 | Minute、MI、N |
| 返回秒信息 | Second、SS、SS |
| 返回毫秒信息 | MillSecond、MS |
SELECT DATEPART(YY,'2017-09-12') // 2017
SELECT DATEPART(M,'2017-09-12') // 9
SELECT DATEPART(dw) // 3 当天是星期二，系统默认一周的开始为星期天，所以星期日的值为1
- 获取时间日期指定部分的字符串：DATENAME ( datepart , date )，返回nvarchar。date参数同上。
SELECT DATENAME(YY,'2017-09-12') // 2017
SELECT DATENAME(dw,'2017-09-12') // Tuesday
- 日期加法：DATEADD (datepart , number , date )，返回结果为date + number,返回 datetime2 类型。datepart是与 number相加的date部分，见上表。number是一个表达式，可以解析为与date的 datepart相加的int，用户定义的变量是有效的，如果您指定一个带小数的值，则将小数截去且不进行舍入。date同上。
SELECT DATEADD (YY , 2 , '2017-09-12' ) // 2019-09-12 00:00:00.000
SELECT DATEADD (DW , 2 , '2017-09-12' ) // 2017-09-14 00:00:00.000
SELECT DATEADD (M , 3 , '2017-09-12' ) // 2017-12-12 00:00:00.000
- 日期减法：DATEDIFF ( datepart , startdate , enddate )，返回的结果为：enddate - startdate，返回int类型。datepart指定相减的类型，见上表；startdate同上；enddate同上。
SELECT DATEDIFF ( YY , '2017-09-10' , '2018-09-23' ) // 1
SELECT DATEDIFF ( DW , '2017-09-10' , '2018-09-23' ) // 378
SELECT DATEDIFF ( DD , '2017-09-10' , '2018-09-23' ) // 378
- 字符串转日期
select CONVERT(datetime,'2017-11-23 12:34:44') optime;
select CAST('2010-07-25T13:50:38.544' AS datetime) optime;
语法
CONVERT ( data_type [ ( length ) ] , expression [ , style ] );
CAST ( expression AS data_type [ ( length ) ] );
日期运算
- 加
select CONVERT(datetime,'2017-11-23 12:00:00',120) + 5 optime; //2017-11-28 12:00:00.000
日期类型的默认值为1900-01-01 00:00:00.000,即可以认为日期类型的“零”值为1900-01-01 00:00:00.000。所以任何一个日期类型的数据加上值为1900-01-01 00:00:00.000的日期类型数据相当于加的是0，即值不变。若加的值不为“零”，则相当于加上该值与“零”值之间的差的天数，然后该差值再做加法。
select CONVERT(datetime,'2017-11-22 12:00:00',120) + CONVERT(datetime,'1900-01-01 12:00:00',120); //2017-11-23 00:00:00.000
select CONVERT(datetime,'2017-11-22 12:00:00',120) + CONVERT(datetime,'2000-01-01 12:00:00',120);//2117-11-23 00:00:00.000
- 减
select CONVERT(datetime,'2017-11-23 12:00:00',120) - 10 optime; //2017-11-13 12:00:00.000
日期类型的默认值为1900-01-01 00:00:00.000,即可以认为日期类型的“零”值为1900-01-01 00:00:00.000。日期类型相减相当于减数与被减数之间天数之差，该差值再加上日期的“零”值。
select CONVERT(datetime,'2017-11-23 12:00:00',120) - CONVERT(datetime,'2017-11-23 12:00:00',120) optime; //1900-01-01 00:00:00.000
select CONVERT(datetime,'2017-11-23 12:00:00',120) - CONVERT(datetime,'2017-11-22 12:00:00',120) optime; //1900-01-02 00:00:00.000
select CONVERT(datetime,'2017-11-22 12:00:00',120) - CONVERT(datetime,'2017-11-23 12:00:00',120) optime; //1899-12-31 00:00:00.000
select CONVERT(datetime,'2017-11-22 12:00:00',120) - CONVERT(datetime,'2010-11-23 12:00:00',120) optime; //1907-01-01 00:00:00.000
- 日期类型不能做乘除法。
常用日期SQL
- 获取一个月的第一天：
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)