SQL Server - DateFirst Example, Sql Server

Language: Layout:

--------------------------------------------------------------------------------
-- Create Date Table
--------------------------------------------------------------------------------
CREATE TABLE date_t
(
  [TheDate]  DATE NOT NULL PRIMARY KEY
);

--------------------------------------------------------------------------------
-- Create Date list
-- Using Number sequence: http://datablog.roman-halliday.com/index.php/2018/06/01/dates-in-sql-server-create-sample-date-ranges/
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = '01-JAN-17'; -- At time of writing this is the past
DECLARE @DateEnd   DATE = '01-JAN-19'; -- At time of writing this is the future
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
INSERT
  INTO date_t
      ([TheDate])
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
 ORDER BY concatenate_calculation
;

--------------------------------------------------------------------------------
-- Format to get results
--------------------------------------------------------------------------------
SELECT @@DateFirst AS [DateFirst Setting]; -- Default

--Set the start of the week to Monday
SET DATEFIRST 1;

SELECT @@DateFirst AS [DateFirst Setting];

SELECT [TheDate],
       DateName(dw, [TheDate]) AS TheDate_day_name,
       DatePart(dw, [TheDate]) AS TheDate_day_of_week,
       DatePart(dd, [TheDate]) AS TheDate_day_of_month,
       DatePart(dy, [TheDate]) AS TheDate_day_of_year,
       DatePart(wk, [TheDate]) AS TheDate_week_of_year
  FROM date_t
 WHERE [TheDate] BETWEEN '29-DEC-17'
                     AND '20-JAN-18'
;

--Set the start of the week to Sunday
SET DATEFIRST 7;

SELECT @@DateFirst AS [DateFirst Setting];

View schema
Execution time: 0,03 sec, rows selected: 51, rows affected: 731, absolute service time: 0,2 sec