SQL Script to get the Date Dimension column values

 In DW Date Dimension is very important. Below is the script to get the Date Dimension value.

DECLARE @Date DATE   SET @Date='2021-06-21'   SELECT @Date                                                               AS        [Date],        Year(@Date) * 10000 + Month(@Date) * 100 + Day(@Date)               AS        [DateKey],        Day(@Date)                                                          AS        [Day Number],        Datename(day, @Date)                                                AS        [Day],        Cast(Datepart(dy, @Date) AS NVARCHAR(5))                            AS        [Day of Year],        Datepart(day, @Date)                                                AS        [Day of Year Number],        Datename(weekday, @Date)                                            AS        [Day of Week],        Datepart(weekday, @Date)                                            AS        [Day of Week Number],        Datename(week, @Date)                                               AS        [Week of Year],        Datename(month, @Date)                                              AS        [Month],        Substring(Datename(month, @Date), 1, 3)                             AS        [Short Month],        N'Q' + Datename(quarter, @Date)                                     AS        [Quarter],        N'H' + CASE WHEN Datepart(month, @Date) < 7 THEN N'1' ELSE N'2' END AS        [Half of Year],        Cast(Datename(year, @Date) + N'-'             + Datename(month, @Date) + N'-01' AS DATE)                     AS        [Beginning of Month],        CASE          WHEN Month(@Date) BETWEEN 1 AND 3 THEN Cast(          Datename(year, @Date) + '-01-01' AS DATE)          WHEN Month(@Date) BETWEEN 4 AND 6 THEN Cast(          Datename(year, @Date) + '-04-01' AS DATE)          WHEN Month(@Date) BETWEEN 7 AND 9 THEN Cast(          Datename(year, @Date) + '-07-01' AS DATE)          WHEN Month(@Date) BETWEEN 10 AND 12 THEN Cast(          Datename(year, @Date) + '-10-01' AS DATE)        END                                                                 AS        [Beginning of Quarter],        CASE          WHEN Datepart(month, @Date) < 7 THEN Cast(          Datename(year, @Date) + '-01-01' AS DATE)          ELSE Cast(Datename(year, @Date) + '-07-01' AS DATE)        END                                                                 AS        [Beginning of Half of Year],        Cast(Datename(year, @Date) + N'-01-01' AS DATE)                     AS        [Beginning of Year],        N'Beginning of Month '        + Datename(month, @Date) + N'-'        + Datename(year, @Date)                                             AS        [Beginning of Month Label],        N'BOM '        + Substring(Datename(month, @Date), 1, 3) + N'-'        + Datename(year, @Date)                                             AS        [Beginning of Month Label Short],        CASE          WHEN Month(@Date) BETWEEN 1 AND 3 THEN N'Beginning Of Quarter '                                                 + Datename(year, @Date) + N'-Q1'          WHEN Month(@Date) BETWEEN 4 AND 6 THEN N'Beginning Of Quarter '                                                 + Datename(year, @Date) + N'-Q2'          WHEN Month(@Date) BETWEEN 7 AND 9 THEN N'Beginning Of Quarter '                                                 + Datename(year, @Date) + N'-Q3'          WHEN Month(@Date) BETWEEN 10 AND 12 THEN          N'Beginning Of Quarter '          + Datename(year, @Date) + N'-Q4'        END                                                                 AS        [Beginning of Quarter Label],        CASE          WHEN Month(@Date) BETWEEN 1 AND 3 THEN          N'BOQ ' + Datename(year, @Date) + N'-Q1'          WHEN Month(@Date) BETWEEN 4 AND 6 THEN          N'BOQ ' + Datename(year, @Date) + N'-Q2'          WHEN Month(@Date) BETWEEN 7 AND 9 THEN          N'BOQ ' + Datename(year, @Date) + N'-Q3'          WHEN Month(@Date) BETWEEN 10 AND 12 THEN          N'BOQ ' + Datename(year, @Date) + N'-Q4'        END                                                                 AS        [Beginning of Quarter Label Short],        CASE          WHEN Datepart(month, @Date) < 7 THEN N'Beginning of Half Year '                                               + Datename(year, @Date) + N'-H1'          ELSE N'Beginning of Half Year '               + Datename(year, @Date) + N'-H2'        END                                                                 AS        [Beginning of Half Year Label],        CASE          WHEN Datepart(month, @Date) < 7 THEN          N'BOH ' + Datename(year, @Date) + N'-H1'          ELSE N'BOH ' + Datename(year, @Date) + N'-H2'        END                                                                 AS        [Beginning of Half Year Label Short],        N'Beginning of Year '        + Datename(year, @Date)                                             AS        [Beginning of Year Label],        N'BOY ' + Datename(year, @Date)                                     AS        [Beginning of Year Label Short],        Datename(month, @Date) + N' '        + Datename(day, @Date) + N', '        + Datename(year, @Date)                                             AS        [Calendar Day Label],        Substring(Datename(month, @Date), 1, 3) + N' '        + Datename(day, @Date) + N', '        + Datename(year, @Date)                                             AS        [Calendar Day Label Short],        Datepart(week, @Date)                                               AS        [Calendar Week Number],        N'CY' + Datename(year, @Date) + '-W'        + RIGHT(N'00' + Datename(week, @Date), 2)                           AS        [Calendar Week Label],        Month(@Date)                                                        AS        [Calendar Month Number],        N'CY' + Datename(year, @Date) + N'-'        + Substring(Datename(month, @Date), 1, 3)                           AS        [Calendar Month Label],        Substring(Datename(month, @Date), 1, 3) + N'-'        + Datename(year, @Date)                                             AS        [Calendar Month Year Label],        Datepart(quarter, @Date)                                            AS        [Calendar Quarter Number],        N'CY' + Datename(year, @Date) + N'-Q'        + Datename(quarter, @Date)                                          AS        [Calendar Quarter Label],        N'Q' + Datename(quarter, @Date) + N'-'        + Datename(year, @Date)                                             AS        [Calendar Quarter Year Label],        CASE          WHEN Datepart(month, @Date) < 7 THEN 1          ELSE 2        END                                                                 AS        [Calendar Half of Year Number],        N'CY' + Datename(year, @Date) + N'-H' + CASE WHEN Datepart(month, @Date)        < 7        THEN N'1' ELSE N'2' END                                             AS        [Calendar Half of Year Label],        N'H' + CASE WHEN Datepart(month, @Date) < 7 THEN N'1' ELSE N'2' END +        N'-' +        Datename(year, @Date)                                               AS        [Calendar Year Half of Year Label],        Year(@Date)                                                         AS        [Calendar Year],        N'CY' + Datename(year, @Date)                                       AS        [Calendar Year Label],        CASE          WHEN Month(@Date) > 6 THEN Month(@Date) - 6          ELSE Month(@Date) + 6        END                                                                 AS        [Fiscal Month Number],        Cast(N'FY' + Cast(CASE WHEN Month(@Date) > 6 THEN Year(@Date) + 1 ELSE             Year(             @Date)             END AS NVARCHAR(4) ) + N'-'             + Substring(Datename(month, @Date), 1, 3) AS NVARCHAR(20))     AS        [Fiscal Month Label],        CASE          WHEN Month(@Date) > 6 THEN Datepart(quarter, @Date) - 2          ELSE Datepart(quarter, @Date) + 2        END                                                                 AS        [Fiscal Quarter Number],        N'FY' + Cast(CASE WHEN Month(@Date) > 6 THEN Year(@Date) + 1 ELSE Year(        @Date)        END AS NVARCHAR(4)) + N'-Q' + CASE WHEN Month(@Date) > 6 THEN Cast(        Datepart(        quarter, @Date) - 2 AS NVARCHAR(2) ) ELSE Cast(Datepart(quarter, @Date) +        2 AS        NVARCHAR(2) ) END                                                   AS        [Fiscal Quarter Label],        CASE          WHEN Month(@Date) > 6 THEN 1          ELSE 2        END                                                                 AS        [Fiscal Half of Year Number],        N'FY' + Cast(CASE WHEN Month(@Date) > 6 THEN Year(@Date) + 1 ELSE Year(        @Date)        END AS NVARCHAR(4)) + N'-H' + CASE WHEN Month(@Date) > 6 THEN N'1' ELSE        N'2' END                                                                            AS        [Fiscal Half of Year Label],        CASE          WHEN Month(@Date) > 6 THEN Year(@Date) + 1          ELSE Year(@Date)        END                                                                 AS        [Fiscal Year],        N'FY' + Cast(CASE WHEN Month(@Date) > 6 THEN Year(@Date) + 1 ELSE Year(        @Date)        END AS NVARCHAR(4))                                                 AS        [Fiscal Year Label],        Year(@Date) * 10000 + Month(@Date) * 100 + Day(@Date)               AS        [Date Key],        Year(@Date) * 100 + Datepart(week, @Date)                           AS        [Year Week Key],        Year(@Date) * 100 + Month(@Date)                                    AS        [Year Month Key],        Year(@Date) * 10 + Datepart(quarter, @Date)                         AS        [Year Quarter Key],        Year(@Date) * 10 + CASE                             WHEN Datepart(month, @Date) < 7 THEN 1                             ELSE 2                           END                                              AS        [Year Half of Year Key],        Year(@Date)                                                         AS        [Year Key],        CASE          WHEN Month(@Date) > 6 THEN ( Year(@Date) + 1 ) * 100 + Month(@Date)          ELSE Year(@Date) * 100 + Month(@Date)        END                                                                 AS        [Fiscal Year Month Key],        ( Year(@Date) * 10000 ) + ( Month(@Date) * 100 ) + 1                AS        [Beginning of Month Key],        CASE          WHEN Month(@Date) BETWEEN 1 AND 3 THEN ( Year(@Date) * 10000 ) + 0101          WHEN Month(@Date) BETWEEN 4 AND 6 THEN ( Year(@Date) * 10000 ) + 0401          WHEN Month(@Date) BETWEEN 7 AND 9 THEN ( Year(@Date) * 10000 ) + 0701          WHEN Month(@Date) BETWEEN 10 AND 12 THEN ( Year(@Date) * 10000 ) + 1001        END                                                                 AS        [Beginning of Quarter Key],        CASE          WHEN Datepart(month, @Date) < 7 THEN ( Year(@Date) * 10000 ) + 0101          ELSE ( Year(@Date) * 10000 ) + 0701        END                                                                 AS        [Beginning of Half of Year Key],        ( Year(@Date) * 10000 ) + 0101                                      AS        [Beginning of Year Key],        CASE          WHEN Month(@Date) > 6 THEN ( ( Year(@Date) + 1 ) * 10 ) +                                     Datepart(quarter, @Date) - 2          ELSE ( Year(@Date) * 10 ) + Datepart(quarter, @Date) + 2        END                                                                 AS        [Fiscal Year Quarter Key],        CASE          WHEN Month(@Date) > 6 THEN ( ( Year(@Date) + 1 ) * 10 ) + 1          ELSE ( Year(@Date) * 10 ) + 2        END                                                                 AS        [Fiscal Year Half of Year Key],        Datepart(iso_week, @Date)                                           AS        [ISO Week Number];

Running this script.