** SQL Server ** ** 时间格式浅析 **
数据库中时间日期往往是一个很重要的数据。各个计算机上的时间往往不同,为了在数据库中插入统一的时间,如果是取当前时刻,最好直接从数据库服务器读取。比如有一个表名为 tablename 的表格,其中 columnname 字段是当前记录插入时的当前时间,则该插入语句应写为: insert into table name (columnname,…) values (GetDate(),…) 。这样 GetDate() 函数将数据库服务器的当前时间插入该记录中。
在查找所有当天的记录前,先来分析一下 T-SQL 的时间日期表示方式。在 T-SQL 中,时间日期格式数据类型实际上是一个浮点数类型,记录的是当前时间到 1900 年 1 月 1 日 0 时的天数,加上剩余的时间化成小数。下面的语句:
select getdate() as 当前时间 ,cast(getdate() as float) as 距 1900 年 1 月 1 日天数
将返回:
当前时间 距 1900 年 1 月 1 日天数
--------------------------- -----------------------------------------------------
2001-11-10 11:05:35.733 37203.462219135799
(1 row(s) affected)
所以要查找当天插入的记录,理所应当的会想到用 GetDate() 获取两个当前时间,并将前一个用 Convert 或 Cast 函数转换为整数(记为 IntToday ),将后一个转换为浮点数(记为 FloatNow ),再将需要查找的记录日期也转换为浮点数(记为 FloatCheck ),只要使用条件 ”…where FloatCheck between IntToday and FloatNow” 就可以找出哪些时间是属于今天的时间。现在看起来确实如此。
表格 testtable 有三个字段, id 是一个自动增长的主键, inserttime 是记录插入时刻的时间, comment 字段是为了方便观察设立的。该表格中共有四条记录,前两条记录是 11 月 9 日插入的,后两条是 11 月 10 日插入的。
第一条 SQL 语句返回该表格中的所有记录。
select * from testtable
id inserttime comment
------------ -------------------------------------- --------------------------------------------
1 2001-11-09 10:28:42.943 第一条记录
2 2001-11-09 17:43:22.503 第二条记录
3 2001-11-10 11:29:11.907 第三条记录
4 2001-11-10 11:29:51.553 第四条记录
(4 row(s) affected)
下面的语尝试选出今天( 2001 年 11 月 10 日)插入的记录,该语句执行时的数据库服务器时间是 2001-11-10 11:40:57.800
select * from testtable where cast(inserttime as float) between cast(GetDate() as int) and cast(GetDate() as float)
id inserttime comment
------------ -------------------------------------- --------------------------------------------
3 2001-11-10 11:29:11.907 第三条记录
4 2001-11-10 11:29:51.553 第四条记录
(2 row(s) affected)
这条语句成功地过滤了前一天产生的记录。
现在地 11 点 51 分,午餐时间到了,我要等一会再继续工作。
12 点 26 分,开始工作前让我先欣赏一下自己的“杰作”。可是,出问题了,那条用来过滤的语句什么也没有返回。去掉 where 子句再执行,原来的记录还在。也就是说数据库里的记录不再满足条件了。没有别的办法,让我们来看看条件发生了什么变化。
执行语句:
select cast(inserttime as float) as FloatCheck,cast(GetDate() as int) as IntToday,cast(GetDate() as float) as FloatNow from testtable
结果返回:
FloatCheck IntToday FloatNow
-----------------------------------------------------------------------------------
3702.43660814043 37204 3703.524545756176
3702.728274807101 37204 3703.524545756176
3703.478610030863 37204 3703.524545756176
3703.479068904322 37204 3703.524545756176
(4 row(s) affected)
注意到 IntToday 比 FloatNow 大,这就是条件不再满足的原因。原来 cast() 函数并不简单地去掉小数,而是四舍五入,所以下午 (cast(GetDate() as int)) 返回的值比上午返回的要大 1 。在程序中不可能先判断上午和下午再选择 SQL 语句执行,因此有必要对 GetDate() 返回的值做一下数学上的处理。注意到 cast(GetDate() as int) 当天下午返回的值和前一天下午返回的值一样大,我的办法是先将 GetDate() 值减去 0.5 。这样如果是上午,减去 0.5 后变为前一天的下午,小数部分“入”,如果是下午,减去 0.5 后变为当天上午,小数部分“去”。写成的新语句如下:
select * from testtable where cast(inserttime as float) between cast( GetDate()-0.5 as int) and cast(GetDate() as float)
执行结果正常。
另外,事实上 float (也可能是 real )数据类型是 DateTime 数据类型的基本类型,所以它们之间可以透明地比较,也就是说不必进行转换就能直接比较,像这样:
select * from testtable where inserttime between cast(GetDate()-0.5 as int) and GetDate()
我先前做的转换只是为了方便说明。