SQL 在 Unicode 欄位設定 Unicode 文字

在 Unicode 欄位設定 Unicode 文字
必需滿足兩個條件

一、該 sql 檔案編碼為 UTF-8
這是常被忽略的一點，曾經因為檔案編碼為 Big5 而花費幾個小時找問題，
使用 SSMS 前，預設存檔編碼為 Big5，記得自行轉檔。

二、該文字前加上 N 前置詞
一個字串如果沒有加上前置詞，該字串會被當作非 Unicode 解讀，
造成存在 Unicode 欄位時變成亂碼

範例：

UPDATE GameServer.dbo.Game SET Name = N'可愛巧虎島'



資料來源：
在 SQL Server 中處理 Unicode 字串常數時，必需為所有的 Unicode 字串加上前置詞 N
https://support.microsoft.com/zh-tw/kb/239530

SQL - First day of week

SQL - First day of week

Find Monday of week
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
is Wrong. (Not first day of week)
Find Monday of week by first day of week is Sunday.
( 2014/10/05 is Sunday, then this find Monday is 2014/10/06)

Find Sunday of week
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)
is Right.
Find Sunday of week by first day of week is Sunday.
( 2014/10/05 is Sunday, then this find Sunday is 2014/10/05)

Find fist of week by DATEFIRST
SET DATEFIRST 1
DATEADD(day,DATEDIFF(day,0,DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE())),0)
is Great



=== Unit Test ===

-- 2014/10/04 Saturday
-- 2014/10/05 Sunday
-- 2014/10/06 Monday
-- 2014/10/07 Tuesday

-- First day of week is Monday
-- Use 2014/10/07 Tuesday
-- expected '2014/10/06', actual '2014/10/06', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/07'), 0)

-- Use 2014/10/06 Monday
-- expected '2014/10/06', actual '2014/10/06', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/06'), 0)

-- Use 2014/10/05 Sunday
-- expected '2014/09/29', actual '2014/10/06', Wrong
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/05'), 0)

-- Use 2014/10/04 Saturday
-- expected '2014/09/29', actual '2014/09/29', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/04'), 0)

-- Why, because DATEDIFF fist day of week is Sunday,
-- and DATEADD(wk, 0, 0) = 1990/01/01 is Monday

-- So, we can't find Monday of week,
-- but we can find Sunday of week

-- First day of week is Sunday
-- Use 2014/10/07 Tuesday
-- expected '2014/10/05', actual '2014/10/05', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/07'), -1)

-- Use 2014/10/06 Monday
-- expected '2014/10/05', actual '2014/10/05', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/06'), -1)

-- Use 2014/10/05 Sunday
-- expected '2014/10/05', actual '2014/10/05', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/05'), -1)

-- Use 2014/10/04 Saturday
-- expected '2014/09/28', actual '2014/09/28', Right
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2014/10/04'), -1)



-- Test Data
DECLARE @TableTest TABLE
([Date] VARCHAR(20),
[Expected] VARCHAR(20),
[Actual] VARCHAR(20))

-- Test Case 1
INSERT @TableTest
VALUES
('2014/10/04', '2014/09/29', '')
,('2014/10/05', '2014/09/29', '')
,('2014/10/06', '2014/10/06', '')
,('2014/10/07', '2014/10/06', '')

SET DATEFIRST 1

UPDATE @TableTest
SET [Actual] = CONVERT(VARCHAR(20), DATEADD(wk, DATEDIFF(wk, 0, [Date]), 0), 111)

SELECT
*
, CASE WHEN [Expected] = [Actual] THEN 'TRUE' ELSE 'FALSE' END AS Result
FROM @TableTest

-- Test Case 2

DELETE @TableTest

INSERT @TableTest
VALUES
('2014/10/04', '2014/09/28', '')
,('2014/10/05', '2014/10/05', '')
,('2014/10/06', '2014/10/05', '')
,('2014/10/07', '2014/10/05', '')

SET DATEFIRST 7

UPDATE @TableTest
SET [Actual] = CONVERT(VARCHAR(20), DATEADD(wk, DATEDIFF(wk, 0, [Date]), -1), 111)

SELECT
*
, CASE WHEN [Expected] = [Actual] THEN 'TRUE' ELSE 'FALSE' END AS Result
FROM @TableTest

-- Test Case 3

DELETE @TableTest

INSERT @TableTest
VALUES
('2014/10/04', '2014/09/29', '')
,('2014/10/05', '2014/09/29', '')
,('2014/10/06', '2014/10/06', '')
,('2014/10/07', '2014/10/06', '')

SET DATEFIRST 1

UPDATE @TableTest
SET [Actual] = CONVERT(VARCHAR(20), DATEADD(day,DATEDIFF(day,0,DATEADD(day, 1 - DATEPART(weekday, [Date]), [Date])),0), 111)

SELECT
*
, CASE WHEN [Expected] = [Actual] THEN 'TRUE' ELSE 'FALSE' END AS Result
FROM @TableTest


-- Test Case 4

DELETE @TableTest

INSERT @TableTest
VALUES
('2014/10/04', '2014/09/28', '')
,('2014/10/05', '2014/10/05', '')
,('2014/10/06', '2014/10/05', '')
,('2014/10/07', '2014/10/05', '')

SET DATEFIRST 7

UPDATE @TableTest
SET [Actual] = CONVERT(VARCHAR(20), DATEADD(day,DATEDIFF(day,0,DATEADD(day, 1 - DATEPART(weekday, [Date]), [Date])),0), 111)

SELECT
*
, CASE WHEN [Expected] = [Actual] THEN 'TRUE' ELSE 'FALSE' END AS Result
FROM @TableTest


-- Test Case 5

DELETE @TableTest

INSERT @TableTest
VALUES
('2014/10/04', '2014/09/30', '')
,('2014/10/05', '2014/09/30', '')
,('2014/10/06', '2014/09/30', '')
,('2014/10/07', '2014/10/07', '')
,('2014/10/08', '2014/10/07', '')

SET DATEFIRST 2

UPDATE @TableTest
SET [Actual] = CONVERT(VARCHAR(20), DATEADD(day,DATEDIFF(day,0,DATEADD(day, 1 - DATEPART(weekday, [Date]), [Date])),0), 111)

SELECT
*
, CASE WHEN [Expected] = [Actual] THEN 'TRUE' ELSE 'FALSE' END AS Result
FROM @TableTest

SQL DATEDIFF 日期差異，同一週、上一週、上上週，指定星期幾為起啟日。

一般來說，希望找到兩個日期是不是同一週可以使用 DATEDIFF 函式，
DATEDIFF(ww, @date1, @date2)

回傳值 0 表示同一週，1 表示 date1 是 date2 上一週，2 表示是上上週，-1 則表示是下一週，-2 是下下週。

而預設是以星期日為一週的開始，想說指定 SET DATEFIRST 1 來改變星期一為起始日。
但是怎麼設定都沒有效果。

上網路查，結果 MSDN 寫著「Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.」。

所以 DATEFIRST 對於 DEATEDIFF 是無效的。

那怎麼辦呢？網友已經提供了解法，解決了這個問題。

DATEDIFF(ww, DATEADD(dd, -@@DATEFIRST, @d1), DATEADD(dd, -@@DATEFIRST, @d2))

範例：
DECLARE @d1 DATETIME
DECLARE @d2 DATETIME
SET @d1 = '2014/10/05'
SET @d2 = '2014/10/06'

SET DATEFIRST 1

SELECT DATEDIFF(ww, DATEADD(dd, -@@DATEFIRST, @d1), DATEADD(dd, -@@DATEFIRST, @d2))

參考資料：
DATEDIFF (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms189794.aspx

Is it possible to set start of week for T-SQL DATEDIFF function?
http://stackoverflow.com/questions/1101892/is-it-possible-to-set-start-of-week-for-t-sql-datediff-function

problem with Datediff and datefirst
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8cc3493a-7ae5-4759-ab2a-e7683165320b/problem-with-datediff-and-datefirst?forum=transactsql