Use Date Part¶

Guide¶

Use case: Need a Date table with many different date parts and reference date columns

This can be implemented all within Vinyl. It does not require a Number table with 0 - N rows, but there is another How-To on how to exponentially generate numbers for a table like that all with Vinyl subqueries unioned together.

Whether your week starts on Sunday or Monday is dependent on your database settings. There is an option to have the global @@DATEFIRST setting be Monday or Sunday at the database level.

Below is the table Date.

The following are queries to create the columns.

CAST(DateAdd(day, N.Number - {{HistoricalDays}}, NOW()) AS Date) AS Date DatePart(year, {{Date}}) AS Year DatePart(quarter, {{Date}}) AS Quarter DatePart(month, {{Date}}) AS Month DatePart(week, {{Date}}) AS Week DatePart(day, {{Date}}) AS Day DatePart(weekday, {{Date}}) AS Weekday DateAdd(year, DateDiff(year, 0, {{Date}}), 0) AS YearStartDate DateAdd(year, DateDiff(year, 0, {{Date}}) + 1, - 1) AS YearEndDate DateAdd(day, -(DatePart(day, {{Date}}) - 1), {{Date}}) AS MonthStartDate DateAdd(day, -(DatePart(day, DateAdd(mm, 1, {{Date}}))), DateAdd(month, 1, {{Date}})) AS MonthEndDate

These last four are for databases with @@DATEFIRST set as Sunday. You can check to see if your Weekday is set to Sunday by seeing if the Weekday value above (DatePart(weekday, {{Date}}) for a Sunday = 1. (If it is = 7 and for a Monday it = 1, these will likely have to change).

DateAdd(day, -(IIF(DatePart(weekday, {{Date}}) = 1, 8, DatePart(weekday, {{Date}})) - 2), {{Date}}) AS WeekStartDateMonday DateAdd(day, 6, {{WeekStartDateMonday}}) AS WeekEndDateMonday DateAdd(day , -(DatePart(weekday, {{Date}}) - 1), {{Date}}) AS WeekStartDateSunday DateAdd(day, 6, {{WeekStartDateSunday}}) AS WeekEndDateSunday

Below are the results given.