SQLServer时间格式浅析

文章类别：Sql Server | 发表日期:2008-10-5 21:36:15

 

 www.chinai tp ow er.com采集不要钱OIJml

SQL Server时间格式浅析www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

数据库中时间日期往往是一个很重要的数据。各个计算机上的时间往往不同，为了在数据库中插入统一的时间，如果是取当前时刻，最好直接从数据库服务器读取。比如有一个表名为tablename的表格，其中columnname字段是当前记录插入时的当前时间，则该插入语句应写为：insert into table name (columnname,…) values (GetDate(),…)。这样GetDate()函数将数据库服务器的当前时间插入该记录中。www.chinai tp ow er.com采集不要钱OIJml

在查找所有当天的记录前，先来分析一下T-SQL的时间日期表示方式。在T-SQL中，时间日期格式数据类型实际上是一个浮点数类型，记录的是当前时间到1900年1月1日0时的天数，加上剩余的时间化成小数。下面的语句：www.chinai tp ow er.com采集不要钱OIJml

select getdate() as 当前时间,cast(getdate() as float) as 距1900年1月1日天数www.chinai tp ow er.com采集不要钱OIJml

将返回：www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

当前时间                        距1900年1月1日天数www.chinai tp ow er.com采集不要钱OIJml

--------------------------- -----------------------------------------------------www.chinai tp ow er.com采集不要钱OIJml

2001-11-10 11:05:35.733                        37203.462219135799www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

(1 row(s) affected)www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

所以要查找当天插入的记录，理所应当的会想到用GetDate()获取两个当前时间，并将前一个用Convert或Cast函数转换为整数（记为IntToday），将后一个转换为浮点数（记为FloatNow），再将需要查找的记录日期也转换为浮点数（记为FloatCheck），只要使用条件”…where FloatCheck between IntToday and FloatNow”就可以找出哪些时间是属于今天的时间。现在看起来确实如此。www.chinai tp ow er.com采集不要钱OIJml

表格testtable有三个字段，id是一个自动增长的主键，inserttime是记录插入时刻的时间，comment字段是为了方便观察设立的。该表格中共有四条记录，前两条记录是11月9日插入的，后两条是11月10日插入的。www.chinai tp ow er.com采集不要钱OIJml

第一条SQL语句返回该表格中的所有记录。www.chinai tp ow er.com采集不要钱OIJml

select * from testtablewww.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

id                    inserttime                              commentwww.chinai tp ow er.com采集不要钱OIJml

------------ -------------------------------------- --------------------------------------------www.chinai tp ow er.com采集不要钱OIJml

1                   2001-11-09 10:28:42.943                    第一条记录www.chinai tp ow er.com采集不要钱OIJml

2                   2001-11-09 17:43:22.503                    第二条记录www.chinai tp ow er.com采集不要钱OIJml

3                   2001-11-10 11:29:11.907                     第三条记录www.chinai tp ow er.com采集不要钱OIJml

4                   2001-11-10 11:29:51.553                    第四条记录www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

(4 row(s) affected)www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

下面的语尝试选出今天（2001年11月10日）插入的记录，该语句执行时的数据库服务器时间是2001-11-10 11:40:57.800www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

select * from testtable where cast(inserttime as float) between cast(GetDate() as int) and cast(GetDate() as float)www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

id                    inserttime                              commentwww.chinai tp ow er.com采集不要钱OIJml

------------ -------------------------------------- --------------------------------------------www.chinai tp ow er.com采集不要钱OIJml

3                   2001-11-10 11:29:11.907                     第三条记录www.chinai tp ow er.com采集不要钱OIJml

4                   2001-11-10 11:29:51.553                    第四条记录www.chinai tp ow er.com采集不要钱OIJml

(2 row(s) affected)www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

这条语句成功地过滤了前一天产生的记录。www.chinai tp ow er.com采集不要钱OIJml

现在地11点51分，午餐时间到了，我要等一会再继续工作。www.chinai tp ow er.com采集不要钱OIJml

12点26分，开始工作前让我先欣赏一下自己的“杰作”。可是，出问题了，那条用来过滤的语句什么也没有返回。去掉where子句再执行，原来的记录还在。也就是说数据库里的记录不再满足条件了。没有别的办法，让我们来看看条件发生了什么变化。www.chinai tp ow er.com采集不要钱OIJml

执行语句：www.chinai tp ow er.com采集不要钱OIJml

select cast(inserttime as float) as FloatCheck,cast(GetDate() as int) as IntToday,cast(GetDate() as float) as FloatNow from testtablewww.chinai tp ow er.com采集不要钱OIJml

结果返回：www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

FloatCheck                      IntToday                              FloatNowwww.chinai tp ow er.com采集不要钱OIJml

-----------------------------------------------------------------------------------www.chinai tp ow er.com采集不要钱OIJml

3702.43660814043                    37204                   3703.524545756176www.chinai tp ow er.com采集不要钱OIJml

3702.728274807101                   37204                   3703.524545756176www.chinai tp ow er.com采集不要钱OIJml

3703.478610030863                   37204                   3703.524545756176www.chinai tp ow er.com采集不要钱OIJml

3703.479068904322                   37204                   3703.524545756176www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

(4 row(s) affected)www.chinai tp ow er.com采集不要钱OIJml

 www.chinai tp ow er.com采集不要钱OIJml

注意到IntToday比FloatNow大，这就是条件不再满足的原因。原来cast()函数并不简单地去掉小数，而是四舍五入，所以下午(cast(GetDate() as int))返回的值比上午返回的要大1。在程序中不可能先判断上午和下午再选择SQL语句执行，因此有必要对GetDate()返回的值做一下数学上的处理。注意到cast(GetDate() as int)当天下午返回的值和前一天下午返回的值一样大，我的办法是先将GetDate()值减去0.5。这样如果是上午，减去0.5后变为前一天的下午，小数部分“入”，如果是下午，减去0.5后变为当天上午，小数部分“去”。写成的新语句如下：www.chinai tp ow er.com采集不要钱OIJml

select * from testtable where cast(inserttime as float) between cast(GetDate()-0.5 as int) and cast(GetDate() as float)www.chinai tp ow er.com采集不要钱OIJml

执行结果正常。www.chinai tp ow er.com采集不要钱OIJml

另外，事实上float（也可能是real）数据类型是DateTime数据类型的基本类型，所以它们之间可以透明地比较，也就是说不必进行转换就能直接比较，像这样：www.chinai tp ow er.com采集不要钱OIJml

select * from testtable where inserttime between cast(GetDate()-0.5 as int) and GetDate()www.chinai tp ow er.com采集不要钱OIJml

我先前做的转换只是为了方便说明。www.chinai tp ow er.com采集不要钱OIJml

上一篇：{技巧}SQLServer时间格式浅析 人气:2080
下一篇：{技巧}利用SQLServer的全局临时表防止用户重复登录 人气:1609

Sql Server视频教程