locked
need calender in sql query RRS feed

  • Question

  • User-807418713 posted

    Hello

    I need Calendar Like this format in sql code

    Consider current month is March-2018 based on this i need output like this below format

    Tuesday, August 7, 2018 12:45 PM

Answers

  • User77042963 posted
    Declare @startdate date='2018-03-01'
    Declare @enddate date=dateadd(month,12,@startdate)
     
    
     declare @ColumnHeaders VARCHAR(MAX)
     Declare @sql as NVarchar(4000)=null
    
    -- Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
     
    ,dates as (
    Select n, DATEADD(day, n-1, @startdate ) dt  
    from(Select n from nums) D(n)
    WHERE  DATEADD(day, n-1, @startdate )<@enddate
    )
    --hold dates in a temp table
    Select n, dt
    into dttemp
    from dates
    
     
       
    Select @ColumnHeaders = STUFF( (SELECT   ',' + 'max (Case when day(dt)='+ quotename(day(dt),'''') +' then '+ quotename(day(dt),'''')+' else null end) as ' + Quotename(day(dt),'[')  + char(10)+char(13)
    FROM  dttemp
    WHERE month(dt)=1
    Order by n
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
       
     --print @ColumnHeaders
     
    SET @sql = N'Select datename(month,dt)+'' - ''+Cast(Year(dt) as char(4)), '
    + @ColumnHeaders 
    +' from dttemp
    Group by datename(month,dt)+'' - ''+Cast(Year(dt) as char(4)),  Year(dt),month(dt)  
    Order by Year(dt),month(dt) '
     
    --print @sql
     EXEC sp_executesql @sql;
    
    
     drop table dttemp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 7, 2018 2:50 PM

All replies

  • User77042963 posted

    Hello

    I need Calendar Like this format in sql code

    Consider current month is March-2018 based on this i need output like this below format

    Declare @startdate date='2018-03-01'
    Declare @enddate date=dateadd(month,12,@startdate)
     
    
     declare @ColumnHeaders VARCHAR(MAX)
     Declare @sql as NVarchar(4000)=null
    
    -- Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
     
    ,dates as (
    Select n, DATEADD(day, n-1, @startdate ) dt  
    from(Select n from nums) D(n)
    WHERE  DATEADD(day, n-1, @startdate )<@enddate
    )
    --hold dates in a temp table
    Select n, dt
    into dttemp
    from dates
    
     
       
    Select @ColumnHeaders = STUFF( (SELECT   ',' + 'max (Case when day(dt)='+ quotename(day(dt),'''') +' then '+ quotename(day(dt),'''')+' else null end) as ' + Quotename(day(dt),'[')  + char(10)+char(13)
    FROM  dttemp
    WHERE month(dt)=1
    Order by n
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
       
     --print @ColumnHeaders
     
    SET @sql = N'Select Format(dt,''MMMM-yyyy''), '
    + @ColumnHeaders 
    +' from dttemp
    Group by Format(dt,''MMMM-yyyy''),  Year(dt),month(dt)  
    Order by Year(dt),month(dt) '
     
    --print @sql
     EXEC sp_executesql @sql;
    
    
     drop table dttemp

    Tuesday, August 7, 2018 2:25 PM
  • User-807418713 posted

    Hello

    Im using sql server 2008 r2

    error

    Msg 195, Level 15, State 10, Line 1
    'Format' is not a recognized built-in function name.

    Tuesday, August 7, 2018 2:27 PM
  • User77042963 posted
    Declare @startdate date='2018-03-01'
    Declare @enddate date=dateadd(month,12,@startdate)
     
    
     declare @ColumnHeaders VARCHAR(MAX)
     Declare @sql as NVarchar(4000)=null
    
    -- Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
     
    ,dates as (
    Select n, DATEADD(day, n-1, @startdate ) dt  
    from(Select n from nums) D(n)
    WHERE  DATEADD(day, n-1, @startdate )<@enddate
    )
    --hold dates in a temp table
    Select n, dt
    into dttemp
    from dates
    
     
       
    Select @ColumnHeaders = STUFF( (SELECT   ',' + 'max (Case when day(dt)='+ quotename(day(dt),'''') +' then '+ quotename(day(dt),'''')+' else null end) as ' + Quotename(day(dt),'[')  + char(10)+char(13)
    FROM  dttemp
    WHERE month(dt)=1
    Order by n
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
       
     --print @ColumnHeaders
     
    SET @sql = N'Select datename(month,dt)+'' - ''+Cast(Year(dt) as char(4)), '
    + @ColumnHeaders 
    +' from dttemp
    Group by datename(month,dt)+'' - ''+Cast(Year(dt) as char(4)),  Year(dt),month(dt)  
    Order by Year(dt),month(dt) '
     
    --print @sql
     EXEC sp_executesql @sql;
    
    
     drop table dttemp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 7, 2018 2:50 PM