SQLServer2008中日期类型数据的存储

发表于 | 更新于 | 评论数： | 阅读次数：

日期类型比较

SQL Server 2008中有datetime、datetime2、datetimeoffset三种数据类型用于存储时间类型数据。它们之间的差别主要有：

  • datetime、datetime2不存储时区信息，datetimeoffset存储时区信息。
  • datetime与datetime2区别是两者存储字节大小和精度不同，后者优于前者。

因此，在存储时间信息时，优先使用datetimeoffset数据类型。当使用datetime和datetime2数据类型时，因没有时区信息，很多客户端链接库会将存储的时间默认为UTC时间。例如：

SQL Server2008中存储某人的出生日期为“1970-01-01 00:00:00”，通过sequelizejs框架读取得到出生日期将是“1970-01-01T00:00:00Z”，这里 T 仅仅是分隔日期和时间的符号，没有其他含义，而 Z 表示 UTC 时间。

这时需要根据自己所在时区对时间进行修正。最简单的方法是在SQL语句中进行修正，代码如下：

1
select birthday1, dateadd(hour,-8,birthday) from students

日期转换

有时时间会以字符串类型存储在数据库中，当需要对时间进行比较时，需要先将字符串类型时间转换成日期类型，可使用convert函数进行转换，例如：

1
2
3
4
# 字符串转日期
select convert(date, '2005-12-31 23:59:59.9999999')
# 日期转字符串
select convert(varchar, getdate(), 1)

其中date是要转换的数据的类型，’2005-12-31 23:59:59.9999999’是被转换的数据。

日期比较

在mssql中比较两个日期的大小，可采用DATEDIFF函数，其语法如下：

1
DATEDIFF ( datepart , startdate , enddate )

其中，datepart是时间单位，startdate和enddate是合法的日期表达式。datediff用法示例如下：

1
2
3
4
5
6
7
8
9
10
11
SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

参考链接

  1. How to add hours to current date in SQL Server?,by stackoverflow.
  2. Choosing between DateTime, DateTimeOffset, TimeSpan, and TimeZoneInfo,by microsoft.
  3. datetime、datetime2的区别,by happymagic.
  4. DateTime , DateTime2 ,DateTimeOffset 之间的小区别,by 神崎橙.
  5. 关于“时间”的一次探索,by KohPoll.
  6. Date and Time Conversions Using SQL Server,by Edgewood Solutions.
  7. DATEDIFF (Transact-SQL),by microsoft.