Monday, July 13, 2020

Calendar table , again...

Hello friends.
I am back to start writing posts about all things that related to SQL Server. There are lots of examples over a internet about how create a calendar table in T-SQL. 
I would like to show you a little bit different technique to create a calendar table which based on the time interval. Let say we need to create a table with one hour interval, I have done it recently for our BI team to create Power BI reports.
Simple we are going to use a recursivequery to create such table.

with tmp(plant_date) as
(
   select CAST('20200101' as datetime)  --- Start date
   union all

      select DATEADD(hour, 1, plant_date)
     from tmp
    where DATEADD(hour, 1, plant_date)   < '20281231'
)/*After we create a simple table  you can extract an year , month, a day and etc.*/
select plant_date,cast(plant_date as date) date,YEAR(plant_date) Year,
CASE WHEN DATEPART(MONTH, plant_date)<10 THEN '0'
              +
              CAST(DATEPART(MONTH, plant_date) AS VARCHAR(2))
              ELSE CAST(DATEPART(MONTH, plant_date) AS VARCHAR(2))
              END Month,
                DATENAME(MONTH,plant_date),
              CASE WHEN DATEPART(day, plant_date)<10 THEN '0'
              +
              CAST(DATEPART(day, plant_date) AS VARCHAR(2))
              ELSE CAST(DATEPART(day, plant_date) AS VARCHAR(2)) END day,
              DATEPART(hour,plant_date) hour
  from  tmp
option (maxrecursion 0)

I hope the above script helps  you.