資料庫中儲存日期的欄位類型到底應該用varchar還是datetime ？，varchardatetime

資料庫中儲存日期的欄位類型到底應該用varchar還是datetime ？，varchardatetime

        背景：

       前段時間在百度經驗看到一篇文章《怎樣在電腦右下角顯示你(愛人)的名字》，之前也聽過這個小技巧，但沒真正動手設定過，所以出於好奇就實踐了一下。

      設定完畢後的效果如下，右下角的時間地區增加了我的名字 “Danny” ：

      

      以上為背景。沒想到這個小技巧給我帶來了麻煩（當然也是一次學習和提高的機會）。

       該字串未被識別偽有效DateTime

       正在做的新聞發布系統，資料庫中儲存時間的欄位類型為datetime類型，並且欄位值都是在伺服器端自動擷取的。想在用戶端以“yyyy-MM-dd HH:mm:ss”的格式顯示時間時，出現了一個問題：“該字串未被識別偽有效DateTime”：

         錯誤頁面如：

         

        出錯關鍵代碼為：    

lblCreateTime.Text = Convert.ToDateTime(news.CreateTime).ToString();  //【注】：lblCreate為最上層顯示頁面一個Lable；news為查詢後得到的“新聞”實體類，CreateTime為它的一個欄位

        猜測是我本機電腦時間格式的問題，在用戶端擷取了一下時間news.CreateTime的值，格式為：“2014/8/23 星期六 Danny 12:42:10”，而該條記錄的時間在資料庫中儲存的值為 “2014-08-23 12:42:10”。經過測試，如果news.CreateTime在資料庫中儲存的類型為varchar()，則不會產生此錯誤。於是可以知道，這裡時間格式轉化的過程是這樣的：

         

        在這個過程中，系統判斷出從資料庫中擷取到的值為datetime類型，所以要將擷取到的值（比如這裡從資料庫中擷取的時間值為“2014-08-23 12:42:10”）轉化為原生時間格式（比如我電腦的時間格式“2014/8/23 星期六 Danny 12:42:10”），在進行最後一步格式轉化時，系統則無法識別使用者自訂的時間格式（比如這裡的“2014/8/23 星期六 Danny 12:42:10”），從而報錯。

        在網上找了兩篇總結Asp.net中時間格式轉化的文章：asp.net 格式化時間日期、Asp.net中時間格式化的幾種方法。這麼多種方法，大體上我把它分為兩個方式：在介面代碼（*.aspx）上轉換 & 在後台代碼（*.aspx.cs）上轉換。

          解決方案

         解決這個問題用了兩個辦法：

        1、如果資料庫中儲存時間的資料類型為datetime，那就避免在後台代碼（*.aspx.cs）中轉化時間格式，將格式轉化的任務放到介面代碼（*.aspx）上；

        比如上面的例子中，無論擷取的時間是什麼格式的，在後台不要對這個時間的值進行任何操作（比如賦值等，否則系統會將時間隱式轉換），而是直接在介面代碼（*.aspx）用DataBinder、Eval等方法來直接進行格式化：

        後台關鍵代碼：    

    <span style="white-space:pre"></span>DataTable dt = new NewsManager().SelectById(newsid);     //這裡得到的dt為從直接資料庫中查詢到的資料    <span style="white-space:pre"></span>    repNews.DataSource =dt;            repNews.DataBind();

        前台關鍵代碼：

<span style="white-space:pre"></span><asp:Repeater ID="repNews" runat="server">            <ItemTemplate>                <p class="con_time">                    發布時間:                     <%# DataBinder.Eval(Container.DataItem,"createTime","{0:yyyy-MM-dd HH:mm:ss}") %>       <%--此處createTime為上面dt中的欄位名--%>                </p>            </ItemTemplate>        </asp:Repeater>

        其實，大部分系統中的時間格式，那些格式轉化函數還是“認識”的，但假如有的將自己的系統時間格式設定為“2014/8/23 星期六Danny 12:42:10”，有的設定為“2014/8/23 星期六胡玉洋 12:42:10”……，這些函數肯定猜不到那麼多中自訂的情況。

        所以，在設計軟體的過程中，最好把用戶端這個因素刨除在外，保證各種使用環境的相容性，時間在資料庫中產生，同樣顯示時也只顯示資料庫中的時間（避免用戶端的過濾）。

        2、將資料庫中儲存時間的資料類型改為varchar()，不過這時最好讓這些時間是資料庫中自動產生的（一個沒有格式的輸入也可能會導致輸出錯誤），因為儲存類型為varchar()，所以擷取到的值也就被認為是一個字串，這時在轉換時間格式時就少了中【將擷取的時間轉化為用戶端時間格式下的值】的步驟，直接將資料庫中的時間字串進行轉化（這時那些轉化函數是能識別資料庫中的時間函數的），用戶端的時間格式不再影響轉換過程。

        不過資料庫中儲存時間的類型如果為字元型也會帶來一些麻煩：

        資料庫中的時間僅僅是用來顯示、尋找的，那麼影響還不算大，但如果對時間欄位進行一些演算法如計算星期、DateDiff、DateAdd等，那就麻煩了，尤其實在大型資料查詢中轉換類型是會影響效率的

        總結

        資料庫中儲存日期的欄位類型到底應該用varchar還是datetime ？這兩種方法各有優勢，datetime可以借用sql函數庫中運算函數，增加了時間在各種運算上的效率；而varchar類型則可以在字元編碼上顯出優勢。在 儲存的時間將來不需要進行大量計算 的前提下，可以考慮選擇varchar類型，反之，選擇datetime類型。

        

怎將資料庫表中儲存的datetime類型欄位裡值從資料庫裡取出，並存放到一個變數裡?

在資料庫中可以這樣實現，
1】比如sql2000，sql2005中：

declare @name varchar(100)
select @name=sname from student where s_id=100
print @name

2】Oracle中可以這樣寫：

set serveroutput on;

declare
a varchar2(20);
begin
select sname into a from student where s_id=100;
dbms_output.put_line(a);
end;
 
資料庫中一個表中的一個欄位類型為字元型，用來儲存日期，現在要一個兩個日期內的資料，怎比較？

以下適用於oracle資料庫：

select * from tablename
where to_date(col_name,'YYYYMMDD') between :date1 and :date2

tablename是你的表名，col_name是你用來儲存日期的字元型，date1和date2是你要比較的兩個日期

記住'YYYYMMDD'要根據你表裡存的資料的格式改：
如表裡面存的該欄位格式是20100914，就寫成'YYYYMMDD'
如表裡面存的該欄位格式是2010-09-14，就寫成'YYYY-MM-DD'
如表裡面存的該欄位格式是2010/09/14 12:30:20，就寫成'YYYY/MM/DD HH24:MI:SS'
如表裡面存的該欄位格式是14-SEP-2010，就寫成'DD-MON-YYYY'
。。。