http://sqlqna.blogspot.tw/2013/09/blog-post_5.html
利用數值序列產生當月日曆資料
許多應用案例中，如銷售、生產日報表，希望呈現整個月完整日期之報表，但因偶發狀況造成當日並未生產或銷售，若公司也未定義日曆資料表，則可能要在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)。
以下列出常用特定時間運算SQL語法:
MSSQL
|
ORACLE
| ||
整數日
|
D
|
DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
|
TRUNC(SYSDATE)
|
C
|
CONVERT(CHAR(10), GETDATE(), 120)
|
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
| |
月初
|
D
|
DATEADD(MM, DATEDIFF(MM,0, GETDATE()), 0)
|
TRUNC(SYSDATE, 'MM')
|
C
|
CONVERT(CHAR(7), GETDATE(), 120) + '-01'
|
TO_CHAR(SYSDATE, 'YYYY-MM-"01"')
| |
下月初
|
D
|
DATEADD(MM, DATEDIFF(MM, 0, GETDATE())+1, 0)
|
LAST_DAY(SYSDATE)+1
|
C
|
DATEADD(MM,1,CONVERT(CHAR(7),GETDATE(),120)+'-01')
|
TO_CHAR(ADD_MONTHS(SYSDATE, 1)
, 'YYYY-MM-"01"')
| |
月底
|
D
|
DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1, 0) -1
|
LAST_DAY(SYSDATE)
|
C
|
DATEADD(MM,1,CONVERT(CHAR(7),GETDATE(),120)+'-01')-1
| ||
年初
|
D
|
DATEADD(YY, DATEDIFF(YY,0,GETDATE()), 0)
|
TRUNC(SYSDATE, 'YY')
|
C
|
CONVERT(CHAR(4), GETDATE(), 120) + '-01-01'
|
TO_CHAR(SYSDATE, 'YYYY-"01-01"')
| |
年底
|
D
|
DATEADD(YY, DATEDIFF(YY, 0, GETDATE())+1, 0)-1
| |
C
|
CONVERT(CHAR(4), GETDATE(), 120) + '-12-31'
|
TO_CHAR(SYSDATE, 'YYYY-"12-31"')
| |
本週三
|
D
|
DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0)+2
|
TRUNC(SYSDATE,'DAY')+3
|
C
| |||
季初
|
D
|
DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0)
|
TRUNC(SYSDATE, 'Q')
|
整點
|
D
|
DATEADD(HH, DATEDIFF(HH, 0, GETDATE()), 0)
|
TRUNC(SYSDATE, 'HH24')
|
C
| |||
分鐘
|
D
|
DATEADD(MI, DATEDIFF(MI, 0, GETDATE()), 0)
|
TRUNC(SYSDATE, 'MI')
|
C
| |||
D:Date, C:Char
沒有留言:
張貼留言