SQL Server時(shí)間格式淺析
2024-08-31 00:48:27
供稿：網(wǎng)友
sql server時(shí)間格式淺析
數(shù)據(jù)庫中時(shí)間日期往往是一個(gè)很重要的數(shù)據(jù)。各個(gè)計(jì)算機(jī)上的時(shí)間往往不同，為了在數(shù)據(jù)庫中插入統(tǒng)一的時(shí)間，如果是取當(dāng)前時(shí)刻，最好直接從數(shù)據(jù)庫服務(wù)器讀取。比如有一個(gè)表名為tablename的表格，其中columnname字段是當(dāng)前記錄插入時(shí)的當(dāng)前時(shí)間，則該插入語句應(yīng)寫為：insert into table name (columnname,…) values (getdate(),…)。這樣getdate()函數(shù)將數(shù)據(jù)庫服務(wù)器的當(dāng)前時(shí)間插入該記錄中。
在查找所有當(dāng)天的記錄前，先來分析一下t-sql的時(shí)間日期表示方式。在t-sql中，時(shí)間日期格式數(shù)據(jù)類型實(shí)際上是一個(gè)浮點(diǎn)數(shù)類型，記錄的是當(dāng)前時(shí)間到1900年1月1日0時(shí)的天數(shù)，加上剩余的時(shí)間化成小數(shù)。下面的語句：
select getdate() as 當(dāng)前時(shí)間,cast(getdate() as float) as 距1900年1月1日天數(shù)
將返回：
當(dāng)前時(shí)間 距1900年1月1日天數(shù)
--------------------------- -----------------------------------------------------
2001-11-10 11:05:35.733 37203.462219135799
(1 row(s) affected)
所以要查找當(dāng)天插入的記錄，理所應(yīng)當(dāng)?shù)臅?huì)想到用getdate()獲取兩個(gè)當(dāng)前時(shí)間，并將前一個(gè)用convert或cast函數(shù)轉(zhuǎn)換為整數(shù)（記為inttoday），將后一個(gè)轉(zhuǎn)換為浮點(diǎn)數(shù)（記為floatnow），再將需要查找的記錄日期也轉(zhuǎn)換為浮點(diǎn)數(shù)（記為floatcheck），只要使用條件”…where floatcheck between inttoday and floatnow”就可以找出哪些時(shí)間是屬于今天的時(shí)間。現(xiàn)在看起來確實(shí)如此。
表格testtable有三個(gè)字段，id是一個(gè)自動(dòng)增長(zhǎng)的主鍵，inserttime是記錄插入時(shí)刻的時(shí)間，comment字段是為了方便觀察設(shè)立的。該表格中共有四條記錄，前兩條記錄是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日）插入的記錄，該語句執(zhí)行時(shí)的數(shù)據(jù)庫服務(wù)器時(shí)間是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)
這條語句成功地過濾了前一天產(chǎn)生的記錄。
現(xiàn)在地11點(diǎn)51分，午餐時(shí)間到了，我要等一會(huì)再繼續(xù)工作。
12點(diǎn)26分，開始工作前讓我先欣賞一下自己的“杰作”。可是，出問題了，那條用來過濾的語句什么也沒有返回。去掉where子句再執(zhí)行，原來的記錄還在。也就是說數(shù)據(jù)庫里的記錄不再滿足條件了。沒有別的辦法，讓我們來看看條件發(fā)生了什么變化。
執(zhí)行語句：
select cast(inserttime as float) as floatcheck,cast(getdate() as int) as inttoday,cast(getdate() as float) as floatnow from testtable
結(jié)果返回：
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()函數(shù)并不簡(jiǎn)單地去掉小數(shù)，而是四舍五入，所以下午(cast(getdate() as int))返回的值比上午返回的要大1。在程序中不可能先判斷上午和下午再選擇sql語句執(zhí)行，因此有必要對(duì)getdate()返回的值做一下數(shù)學(xué)上的處理。注意到cast(getdate() as int)當(dāng)天下午返回的值和前一天下午返回的值一樣大，我的辦法是先將getdate()值減去0.5。這樣如果是上午，減去0.5后變?yōu)榍耙惶斓南挛纾?shù)部分“入”，如果是下午，減去0.5后變?yōu)楫?dāng)天上午，小數(shù)部分“去”。寫成的新語句如下：
select * from testtable where cast(inserttime as float) between cast(getdate()-0.5 as int) and cast(getdate() as float)
執(zhí)行結(jié)果正常。
另外，事實(shí)上float（也可能是real）數(shù)據(jù)類型是datetime數(shù)據(jù)類型的基本類型，所以它們之間可以透明地比較，也就是說不必進(jìn)行轉(zhuǎn)換就能直接比較，像這樣：
select * from testtable where inserttime between cast(getdate()-0.5 as int) and getdate()
我先前做的轉(zhuǎn)換只是為了方便說明。
