Obtaining Various Date Information from Date Information in SQL Server

Hello everyone,

In this article, I will try to give information about how to obtain various date information from date information in SQL Server.

You may want to see or use different variations of the date information you have in SQL Server.

You can use some of this information by using the code below.

DECLARE @Date DATE;
SET @Date = GETDATE()
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];

When you run the above query, you will see a result similar to the one below.

As can be seen, we have obtained various historical information.

Good luck to everyone in business and life.