locked
GET ALL DATE of given MONTH and YEAR as Parameter in SQL SERVER RRS feed

  • Question

  • User1152553138 posted
    DECLARE @month TINYINT
    SET @month = 10;
    
    WITH
    CTE_Days AS
    (
    SELECT 
    DATEADD(MONTH, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM CTE_Days
    WHERE 
    D 
    < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))))))
    )
    
    SELECT replace(convert(NVARCHAR,D, 106), ' ', '-') as SelDate FROM CTE_Days where D <= GETDATE()

    The above query is working fine.

    What i need is, i wish to pass both month and year as parameter for the above query to get all DATE of the given month and year ..

    Saturday, October 15, 2016 11:53 AM

Answers

  • User77042963 posted
    DECLARE @month TINYINT
    SET @month = 2;
    
    DECLARE @year INT
    SET @year = 2016;
    
    --construct the first of the passing month and year
    declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
    
    ;WITH
    CTE_Days AS
    (
    SELECT 
    DATEADD(MONTH, @month, DATEADD(month, -MONTH(@fromDate), DATEADD(day, -DAY(@fromDate) + 1, CAST(FLOOR(CAST(@fromDate AS FLOAT)) AS DATETIME)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM CTE_Days
    WHERE 
    D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(@fromDate), DATEADD(day, -DAY(@fromDate) + 1, CAST(FLOOR(CAST(@fromDate AS FLOAT)) AS DATETIME))))))
    )
    
    SELECT replace(convert(NVARCHAR,D, 106), ' ', '-') as SelDate FROM CTE_Days 
     
    
    
     --Another version
    
     
    --DECLARE @month TINYINT
    --SET @month = 10;
    
    --DECLARE @year INT
    --SET @year = 2016;
    
    ----get the beginning of the passing month and year
    --declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
    
    ---get beginning of next month 
    DECLARE @toDate DATETIME =Dateadd(month,1,@fromDate)  
    
    ;WITH mycte
         AS (SELECT @fromDate AS dt
             UNION ALL
             SELECT dt + 1
             FROM   mycte
             WHERE  dt + 1 < @toDate)
    
      SELECT replace(convert(CHAR(11),Dt, 106), ' ', '-') as SelDate FROM mycte

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 17, 2016 2:29 PM

All replies

  • User-2057865890 posted

    Hi Ashraf007,

    DECLARE @month AS INT = 10
    DECLARE @Year AS INT = 2016
    
    ;WITH N(N)AS 
    (SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
    tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
    SELECT N day,replace(convert(NVARCHAR,datefromparts(@year,@month,N), 106), ' ', '-') date FROM tally
    WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))

    reference: http://stackoverflow.com/a/37027841 

    Best Regards,

    Chris

    Monday, October 17, 2016 3:03 AM
  • User1152553138 posted

    EOMONTH

    Actually am using SQL SERVER 2008 R2 ...

    Here 

    datefromparts
    EOMONTH

    the above function are not there in SQL Server 2008 R2 ...

    Any equivalent function
    Monday, October 17, 2016 10:56 AM
  • User77042963 posted
    DECLARE @month TINYINT
    SET @month = 2;
    
    DECLARE @year INT
    SET @year = 2016;
    
    --construct the first of the passing month and year
    declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
    
    ;WITH
    CTE_Days AS
    (
    SELECT 
    DATEADD(MONTH, @month, DATEADD(month, -MONTH(@fromDate), DATEADD(day, -DAY(@fromDate) + 1, CAST(FLOOR(CAST(@fromDate AS FLOAT)) AS DATETIME)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM CTE_Days
    WHERE 
    D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(@fromDate), DATEADD(day, -DAY(@fromDate) + 1, CAST(FLOOR(CAST(@fromDate AS FLOAT)) AS DATETIME))))))
    )
    
    SELECT replace(convert(NVARCHAR,D, 106), ' ', '-') as SelDate FROM CTE_Days 
     
    
    
     --Another version
    
     
    --DECLARE @month TINYINT
    --SET @month = 10;
    
    --DECLARE @year INT
    --SET @year = 2016;
    
    ----get the beginning of the passing month and year
    --declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
    
    ---get beginning of next month 
    DECLARE @toDate DATETIME =Dateadd(month,1,@fromDate)  
    
    ;WITH mycte
         AS (SELECT @fromDate AS dt
             UNION ALL
             SELECT dt + 1
             FROM   mycte
             WHERE  dt + 1 < @toDate)
    
      SELECT replace(convert(CHAR(11),Dt, 106), ' ', '-') as SelDate FROM mycte

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 17, 2016 2:29 PM
  • User1152553138 posted

    DECLARE @month TINYINT
    SET @month = 2;
    
    DECLARE @year INT
    SET @year = 2016;
    
    --construct the first of the passing month and year
    declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
    
    ;WITH
    CTE_Days AS
    (
    SELECT 
    DATEADD(MONTH, @month, DATEADD(month, -MONTH(@fromDate), DATEADD(day, -DAY(@fromDate) + 1, CAST(FLOOR(CAST(@fromDate AS FLOAT)) AS DATETIME)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM CTE_Days
    WHERE 
    D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(@fromDate), DATEADD(day, -DAY(@fromDate) + 1, CAST(FLOOR(CAST(@fromDate AS FLOAT)) AS DATETIME))))))
    )
    
    SELECT replace(convert(NVARCHAR,D, 106), ' ', '-') as SelDate FROM CTE_Days 
     
    
    
     --Another version
    
     
    --DECLARE @month TINYINT
    --SET @month = 10;
    
    --DECLARE @year INT
    --SET @year = 2016;
    
    ----get the beginning of the passing month and year
    --declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
    
    ---get beginning of next month 
    DECLARE @toDate DATETIME =Dateadd(month,1,@fromDate)  
    
    ;WITH mycte
         AS (SELECT @fromDate AS dt
             UNION ALL
             SELECT dt + 1
             FROM   mycte
             WHERE  dt + 1 < @toDate)
    
      SELECT replace(convert(CHAR(11),Dt, 106), ' ', '-') as SelDate FROM mycte

    This work like a charm ...

    Can we pass month as Jan,Feb,Mar,Apr instead of number like 1,2,3,4 ?

    Saturday, October 22, 2016 7:49 AM
  • User77042963 posted

    "Can we pass month as Jan,Feb,Mar,Apr instead of number like 1,2,3,4 ?"

    You can change the code to construct the the first of the passing month and year:

    DECLARE @month char(3)
    SET @month = 'Mar';
    
    DECLARE @year INT
    SET @year = 2016;
    
    --construct the first of the passing month and year
    declare @fromDate datetime = Cast(  @month  +' 01 '+Cast(@year as char(4))  as datetime)
    select @fromDate

    Monday, October 24, 2016 1:30 PM