The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何用簡單的 SQL 技巧取得特定日期是否為週末假日

在 SQL Server 中有個 DATEDIFF 函數可以計算出兩個日期間的差距,例如若要算出兩個日期的差異「天數」可以用以下語法得知:

SELECT DATEDIFF(day, '2005-12-31', '2009-01-21'); 

而若要查出日期在星期幾,可以用 DATEPART 函數,若是星期日就是 1, 星期一就是 2, 依此類推星期六就是 7。但若我們想得知某一天是否為週末假日的話,好像就沒有現成的函數可用了。

我們可以從日期和時間資料類型與函數 (Transact-SQL)得知 SQL Server 的日期格式有範圍限制,且不同的日期格式範圍還不太一樣:

資料類型 範圍 備註
date 0001-01-01 到 9999-12-31 SQL 2008 新增
smalldatetime 1900-01-01 到 2079-06-06  
datetime 1753-01-01 到 9999-12-31  
datetime2 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 SQL 2008 新增

巧的是 1900-01-01 與 1753-01-01 這兩天剛好都是星期一,所以如果我們用 DATEDIFF 函數算出任意日期與這一天的差異天數,再取得除以 7 的餘數,可以算出以下數據:

星期 餘數 備註 整除於 5 的值

星期一

0

工作天

0

星期二

1

工作天

0

星期三

2

工作天

0

星期四

3

工作天

0

星期五

4

工作天

0

星期六

5

週末假日

1

星期天

6

週末假日

1

從上表可知我們若要透過 T-SQL 語法計算該日期是否為週末假日,就可以用以下語法取得 True/False 型態的結果:

select DATEDIFF(DAY, '17530101', '2009-01-19') % 7 / 5   -- 0
select DATEDIFF(DAY, '17530101', '2009-01-20') % 7 / 5   -- 0
select DATEDIFF(DAY, '17530101', '2009-01-21') % 7 / 5   -- 0
select DATEDIFF(DAY, '17530101', '2009-01-22') % 7 / 5   -- 0
select DATEDIFF(DAY, '17530101', '2009-01-23') % 7 / 5   -- 0
select DATEDIFF(DAY, '17530101', '2009-01-24') % 7 / 5   -- 1
select DATEDIFF(DAY, '17530101', '2009-01-25') % 7 / 5   -- 1

select DATEDIFF(DAY, '17530101', tb.CreationOn) % 7 / 5 FROM tb;

這樣就可以透過 T-SQL 算出該日期是否為週末假日了,至少比在程式中計算日期格式簡便且快多了。

相關連結