利用數值序列產生當月日曆資料

許多應用案例中，如銷售、生產日報表，希望呈現整個月完整日期之報表，但因偶發狀況造成當日並未生產或銷售，若公司也未定義日曆資料表，則可能要在AP端或以其他方式解決，在此說明如何應用數值序列以產生當月日曆資料，SQL如下：

功能	SQL (當月日曆)	說明
MSSQL	SELECT number +1  N   , DATEADD(DD, number, CONVERT(CHAR(8), GETDATE(), 120)+'01') DT --INTO #Calendar FROM master.dbo.spt_values WHERE name IS NULL AND number<DAY( DATEADD(MM , 1 , CONVERT(CHAR(8),GETDATE(), 120) +'01' )-1 ) -------- 月初: CONVERT(CHAR(8),GETDATE(), 120) +'01' 月底: DATEADD(MM, 1, CONVERT(CHAR(8),GETDATE(), 120) +'01')-1	月初:利用CONVERT函數以120型式，取得局部日期字串(如2013-09-)，再補上01即為所求。 月底：利用前述月初日期，以DATEADD函數加上一個月可得次月月初日期，減1天即為本月月底。
ORACLE	SELECT LEVEL N       , TRUNC(SYSDATE, 'MM') + LEVEL-1 DT FROM DUAL CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD') -------- 月底：LAST_DAY(SYSDATE) 另外，TO_CHAR(,'DD') 可得到當日日期數值文字，此處預期應用數值才正確，此為少數允許自動轉型之情況。	LAST_DAY為取得月底日期函數

SQL是一種非程序語言，優點在於集合（SET）及大量等運算處理，循序處理或複雜邏輯則否，且程式常用之迴圈（Loop）功能(T-SQL、PL/SQL非一般SQL)，而一般SQL指令並無支援，可參考本範例之概念，搭配數值序列產生額外資料空間以解決此問題。

另外，將應用前述日曆資料，將日期序列轉換為數值序列。

功能	SQL (當月日曆)	說明
MSSQL	SELECT DT   , DATEDIFF(DD               , CONVERT(CHAR(8),GETDATE(), 120) +'01'               ,  DT               ) +1  N   , DT-CAST(CONVERT(CHAR(8),GETDATE(), 120) +'01'               AS datetime) DF FROM #Calendar WHERE 1=1      AND DT >= CONVERT(CHAR(8),GETDATE(), 120) +'01'
ORACLE	SELECT DT      , TRUNC(SYSDATE, 'MM') BOM --月初      , DT - TRUNC(SYSDATE, 'MM') +1 N FROM    (     SELECT LEVEL N           , TRUNC(SYSDATE, 'MM') + LEVEL-1 DT     FROM DUAL     CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD')    )

ORACLE直接使用日期相減，即可得到數值序列。但MSSQL的日期相減卻仍為日期型態，需使用DATEDIFF函數才正確。另外，ORACLE的月初日期可用TRUNC(SYSDATE,'MM')取得，月底日期則為LAST_DAY(SYSDATE)。