Dates.MyDate

Purpose

Calendar Year

Sample Exec

EXEC get.myView @dataMart = 'Dates', @viewName = 'MyDate', @token = '<myToken>', @version = '<202404>'

Response

[Date_Key]            = CONVERT(date, mydate),
[Date_asText]        = REPLACE(CONVERT(VARCHAR, mydate, 106),' ','-'),
[Date_asInt]        = CONVERT(VARCHAR,  mydate, 112),

[Week_Number]        = FORMAT(DATEPART(WEEK, mydate),'00'),
[ISO_Week]            = FORMAT(DATEPART(ISO_WEEK, mydate),'00'),
[Weekday]            = DATEPART(WEEKDAY, mydate),
[Weekday_Name]        = DATENAME(WEEKDAY, mydate),
[Month_Dayof]        = FORMAT(DATEPART(DAY,  mydate),'00'),
[Month]                = FORMAT(DATEPART(MONTH,    mydate),'00'),
[Month_Name]        = DATENAME(MONTH,   mydate),
[Month_Label]        = FORMAT(mydate,'MMM'),
[Year]                = DATEPART(YEAR,    mydate),
[Year_Dayof]        = DATEPART(DAYOFYEAR,   mydate),
[myView_Dayof] =    DATEDIFF(DAY,convert(date, cast(year(getdate())-7 as varchar(10))+'-10-01' ),mydate)+1,

[YYYY-WW]           = concat_ws('-',DATEPART(YEAR,  mydate),format(DATEPART(WEEK,   mydate),'00')), 
[YYYY-MM]           = concat_ws('-',DATEPART(YEAR,  mydate),format(DATEPART(MONTH,  mydate),'00')), 

[Week_Firstof]  = DATEADD(DAY, 1-DATEPART(WEEKDAY, mydate), CONVERT(date, mydate)),
[Week_Lastof]       = DATEADD(DAY, 7-DATEPART(WEEKDAY, mydate), CONVERT(date, mydate)),
[Month_Firstof] = DATEFROMPARTS(YEAR(mydate), MONTH(mydate), 1),
[Month_Lastof]  = EOMONTH(mydate),
[Year_Firstof]  = CONVERT(date,DATEADD(yy, DATEDIFF(yy, 0,  mydate), 0)),
[Year_Lastof]   = CONVERT(date,DATEADD(yy, DATEDIFF(yy, 0, mydate) + 1, -1))

SQL

BMT-DWH-DEV/Dates_MyDate.sql