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