Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL Server - DateFirst Example
-------------------------------------------------------------------------------- -- 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]; 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' ;
run
|
edit
|
history
|
help
0
Exam
bc160402152
nandhu
Availible schedules
mysqltest
Digital Innovation One - SQL Server _2
sqript for print !00 numbers with out loop
Display Multiple Rows as a single Line
SQL HW 1 Appline
db_hotelc