星期五, 2月 17, 2012

[Challenge] 克服日期計算的恐懼

Beyond Relational TSQL Beginner's Challenge 2

  • 說明：
邀請您來挑戰日期計算。許多人對於日期計算有恐懼，而挑戰目標就是要克服日期計算的恐懼。這個挑戰是利用年、月、星期和每個月第幾個星期來計算日期；例如：2010年01月的第二個禮拜日是幾號，答案是 '2010-01-10'
  • 資料來源（題目內有資料來源的 script）
Yr    Mon    Dy  Dyno
-------------------------------
2010  Jan  Sun    2
2005  Jan  Mon    3
1995  Feb  Sun    1
2000  Feb  Wed    4
1982  Mar  Tue    2
2010  Mar  Tue    8
  • 結果
Yr     Mon    Dy   Dyno  Date
----------------------------------------------
1982    Mar    Tue    2   1982-03-09
1995    Feb    Sun    1   1995-02-05
2000    Feb    Wed    4   2000-02-23
2005    Jan    Mon    3   2005-01-17
2010    Jan    Sun    2   2010-01-10
2010    Mar    Tue    8   Invalid Date
  • 規則
    1. 如果這個日期不存在，必須顯示'Invalid Date'
    2. T-SQL 必須為 'SELECT' 或 ';WITH' 開始的單一查詢
    3. 產生的結果必須根據日期排序
沒有留言:

星期五, 2月 10, 2012

[Challenge] 上班時間統計

Beyond Relational TSQL Challenge 2
  • 資料來源（題目內有資料來源的 script）
ID          StartDate               EndDate
----------- ----------------------- -----------------------
1           2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2           2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3           2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4           2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5           2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6           2009-01-27 21:15:00.000 2009-01-28 09:15:00.000
  • 結果
StartDate               EndDate                 Hours       Minutes 
----------------------- ----------------------- ----------- ----------- 
2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 7           0 
2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 15          0 
2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 9           0 
2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 7           45 
2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0           0 
2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 1           15
  • 題目說明
上班時間定義是從星期一至星期五的0800至1700；如果開始日期是星期五中午1200至星期一上午1000，只能計算星期五中午1200至星期五下午1700和星期一上午0800至1200這段時間。
沒有留言:

星期五, 2月 03, 2012

[SQL] 日期欄位資料型態的選擇

利用字串來儲存日期、時間，常常導致處理時，會發生很多問題，印象中在某本書（忘記哪一本）上有提過，金融業彼此交換資料時，會將所有資料以固定長度、格式的字串來進行交換，這是我聽過要用字串來儲存日期的例子，但通常情況下，還是以日期資料型態來儲存，會是比較好的選擇。

以下說明日期欄位用日期型態和字串型態儲存時，需要注意的地方。

  • 日期有效性
利用 ISDATE() 來檢查字串日期是否為有效日期。
SELECT 
    T.Date,
    CASE 
        WHEN ISDATE(T.Date) = 0 THEN 'INVALID'
        ELSE NULL
    END 'DateJudge'
FROM
    (
        SELECT '2012/01/31' AS Date
        UNION ALL
        SELECT '2012/02/31'
        UNION ALL
        SELECT '2012/10/31'
        UNION ALL
        SELECT '2012/11/31'
        UNION ALL
        SELECT '2012/00/01' -- 字串沒處理好，容易出現
        UNION ALL
        SELECT '2012/13/01' -- 字串沒處理好，容易出現
    ) AS T
[SQL] 日期資料型態的選擇
  • 字串日期格式不一致
資料庫內可能出現 2012/02/01、2012-02-01、20120201、2012/2/1 等字串日期儲存格式。
  • 函數使用
使用日期型態，有 SQL Server 內建內建函數可以使用，EX：DATEADD()、DATEDIFF() 等等，方便商業邏輯實現，EX：計算天數、判斷日期為星期幾等；字串型態則須自行撰寫函數處理。
  • 時區
商業邏輯上有需要轉換時區的話，使用日期資料型態來儲存，就可以明顯感覺到便利性啦
沒有留言: