簡述
最近在實習公司幫忙篩選資料庫的資料做報表，經常使用到篩選日期的一些相關SQL函式。經常用到的是以下幾個：
convert( , , )、datediff( , , )、dateadd( , , )、getdate()、datepart( , )
在Microsoft的MSDN(Microsoft Development Network)有對SQL Server用到的日期和時間類型與函數做很詳盡的說明，所以可能函數的詳細用法可以參考這下面這個網址(https://msdn.microsoft.com/zh-tw/library/ms186724(v=sql.120).aspx)，在左邊點選你想查詢的一些定義。以下會簡短介紹跟寫一些函數使用的心得跟時機，如果SQL很熟練應該就可以跳過。
函數介紹
關於convert( , , )
乍看之下這個好像跟日期沒什麼關係，與不屬於時間函數，但與時間息息相關的函數。目的是用於資料類型間的轉換，定義可參考：https://msdn.microsoft.com/zh-tw/library/ms187928(v=sql.120).aspx以下資料引用自上述網址：
語法：CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
引數：data_type、length、expression、style
備註：當轉換成datetime時，可以參考的格式(風格,style)
convert( , ,)在這邊會用到是因ERP(Enterprise Resource Planning)或SFT(Shop Floor Tracking)一些鼎新的資料庫都用字元儲存日期欄位，可能是因為在使用者介面輸入的時候是採取 _ _ _ _/ _ _ / _ _讓現場作業員或生管人員登記資訊，導致如果要用這個日期欄位做計算的話需要轉換資料型態才能跟日期作加減，得到像是相差天數這樣有用的資訊。
而convert還有一個很大作用就是，就算同樣是datetime的是他的格式也不太一樣，為了資料顯示上的一致，有時候也要在語意層convert成一致的格式，這邊可以再google找到很多教學，不贅述，只是買到的系統有時候在設計上不太一致就會需要多一道這樣的手續去處理。
/*常用的幾個convert(datetime,___)：字元轉換成日期*/
/*如果後來轉出excel的Raw data，在篩選的時候其實用日期顯示資料，篩選起來會方便許多，實際案例可見下圖*/
 |
| 左邊是以字元顯示，右邊是以日期顯示。篩選的方便性高下立判。 |
/*convert(char(8),getdate(),112)*/
/*convert(char(6),getdate(),120)*/
裡面有輸出格式可以參考，用120的時機，通常是需要用到時：分：秒這麼精確的時間資料才會轉，畢竟像是SFT的裡面的零組件轉移，需要很準確地知道什麼時候到達下一站，才能準確展握生產狀況。而對於業務、人資這些部門來說有年/月/日能掌握大致的時間就用112顯示，簡單明瞭~
關於datediff( , , )
從字面上看，其實就是日期(date)差(diff-->difference)。有時候需要查詢資料可以用今天往前回推一個日期區間，就可以利用此函數。
一樣先參考定義：https://msdn.microsoft.com/zh-tw/library/ms189794(v=sql.120).aspx
以下資料引用自上述網址：
語法：DATEDIFF ( datepart , startdate , enddate )
引數：datepart、startdate、enddate
回傳值：int
/*用比較數學的表示方法，就是 (enddate-startdate) in unit datepart(yyyy/qq/mm/dd/ww/hh ...and so on)
舉個實際的例子，生管部的採購組人員，希望知道未來七天預計要到貨的採購件或包裝材料。這時候下篩選條件就可以用datediff。假設ERP採購模組的到貨日期存在A table 內的b column，則我們可以先用datediff(dd,getdate(),A.b)得到相差的日期，此處是要查詢未來七日，所以把A.b寫在後者，這樣未來抵達者的相差天數是正數。然後在where的條件內限定寫下( datediff(dd,A.b,getdate())<=7 ) and ( datediff(dd,A.b,getdate())>=0 )。
=========================以下應該是(2)的內容==========================
回歸到前面說的篩選，在製作自動排程的報表時候，主管蠻喜歡定期追蹤一項事務，比方向是這一季業務的接單情況、累計到上個月公司的銷售金額，然而ERP有時提供的模組可能不是這麼彈性、或是跑出來的報表不是挺漂亮的、甚至根本沒有，當然有時候是因為跨模組的資料，本來就沒有這一份，但有辦法連接資料Table去得到。不管如何，在有辦法獲得想要的資料的時候，就要篩選，去蕪存菁，留下想要的資料，這時候日期區段就是一個很好的條件。
日期區段篩選
過去比較剛入門的寫法其實都是寫得蠻沒有彈性的，甚至說有點蠢，可以看下面的例子。
Ex；
TableName.ColumnName between convert(char(6),dateadd(mm,-1,getdate()),112)+'01' and convert(char(6),dateadd(mm,-1,getdate()),112)+'31'
/*TableName.ColumnName是一個資料庫的Table，其中一個欄位，這邊是以字元方式儲存yyyymmdd這樣的資料，也由於不是日期資料所以後面篩選條件也換成字元*/
/*between and 就是代表我限定一個區間*/
/*convert(char(6), , )就是把時間資料轉換成字元以112的規定(yyyymmdd)顯示,然後6是代表我只取前六字元，因為這邊我只需要限定到年月。/*
/*dateadd(,,)就是用來做日期的加減，第一個引數mm是要加減的單位，第二個引數就是加減的數字，可為負值*/
/*後面另外寫的 +'01' 和 +'31' 是作為concatenation等於是把剛剛取出的yyyymm後面加上日期01和31*/
/*這樣就是比較直覺的找出上個月1~31號的所有資料*/
看到這邊都覺得當初自己有點傻，不過因為根本沒底子就來實習抓ERP、SFT跟HR資料，能最快抓到主管要的資料，哪管你怎麼寫哈哈
後面的寫法應該會實用一點，第一段的介紹就到這邊吧~
P.S.公司有簽保密協定，所以還是不要把資料庫的table name、column name打上來，免得有爭議
留言
張貼留言