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()

我先前做的轉換隻是為了方便說明。