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.