SQL Server時間格式淺析 (轉)

SQL Server時間格式淺析 (轉)[@more@] 

 :namespace prefix = o ns = "urn:schemas--com::office" />

SERVER時間格式淺析

 

中時間日期往往是一個很重要的資料。各個上的時間往往不同，為了在資料庫中插入統一的時間，如果是取當前時刻，最好直接從資料庫讀取。比如有一個表名為tablename的表格，其中columnname欄位是當前記錄插入時的當前時間，則該插入語句應寫為：insert into table name (columnname,…) values (GetDate(),…)。這樣GetDate()將資料庫伺服器的當前時間插入該記錄中。

在查詢所有當天的記錄前，先來分析一下T-SQL的時間日期表示方式。在T-SQL中，時間日期格式資料型別實際上是一個浮點數型別，記錄的是當前時間到1900年1月1日0時的天數，加上剩餘的時間化成小數。下面的語句：

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

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