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