Get All Dates of Given Month and Year in SQL Server

Question

I want to get all dates by declaring month and year in SQL server.

Can anyone please share easy lines of SQL code to get it.

For example:

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016
SELECT * from Something

I have tried below things,

DECLARE @month TINYINT=5

;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)
                   )
               )
           ) Dates
    UNION ALL
    SELECT DATEADD(DAY, 1, Dates)
    FROM   CTE_Days
    WHERE  Dates < 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 Dates
FROM   CTE_Days

But I am looking for easy solution with less lines and short answer

Killoran

You'll want to create a date table in your database. You'll then be able to link using the BETWEEN function. – Mending

Reason for down vote please! – Killoran

"This question does not show any research effort". What have you tried before asking here? – Mending

I'm not a down voter myself but I suspect the reason is this has been asked before. You can use a calendar/date table, tally table or recursive CTE. Of those I'd recommend a date table. These are so handy, especially in reporting. – Hambley

@RichBenner - please check my updated answer for you. :) – Killoran

Changed to an upvote :) – Mending

Answer 1

You can't get all days just by declaring the month, you need to add the year as well because of leap years:

DECLARE @date DATE = getdate()

;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 top(day(EOMONTH(@date)))
  N day,
  dateadd(d,N-1, @date) date
FROM tally

Another different solution(by t@clausen):

DECLARE @month AS INT = 5
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,datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))

Spoonerism

Thanks for your answer! It helped to make some changes in my requirement. So now I have added one more variable year to get all dates. – Killoran

@pedram I deleted the part you changed in my answer since it always returned 36 days, Also added a new answer you can use this instead after you specified the requirements – Spoonerism

now it returns 30 rows :( it should return 31 rows :) – Killoran

fixed now. Changed N< to N<= – Spoonerism

Query is near unreadable and provides no explanation as to what you did to achieve the solution. – Pacheco

Answer 2

Same approach as t-clausen, but a more compact:

Declare @year int = 2017, @month int = 11;
WITH numbers
as
(
    Select 1 as value
    UNion ALL
    Select value + 1 from numbers
    where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)
SELECT datefromparts(@year,@month,numbers.value) Datum FROM numbers

Debose

Is this.. a recursive SQL query? That's possible? Can someone explain what is going on. – Squelch

Answer 3

Another CTE based version

DECLARE @Month INT = 2, @Year INT = 2019

;WITH MonthDays_CTE(DayNum)
AS
(
SELECT DATEFROMPARTS(@Year, @Month, 1) AS DayNum
UNION ALL
SELECT DATEADD(DAY, 1, DayNum)
  FROM MonthDays_CTE
  WHERE DayNum < EOMONTH(DATEFROMPARTS(@Year, @Month, 1))
)
SELECT *
  FROM MonthDays_CTE

Tamratamsky

Very Nice! This worked perfectly for me. – Lambent

However, I would like to completely understand it. What is the ALL in UNION ALL for? I tried it without (UNION without ALL) but it throws an error that says basically, you dont have an UNION ALL Operator. I can see that MonthDays_CTE is called recursively, but i would have expected a lot duplicates, because it looks to me that it always starts at the first of a month. So why are there no duplicates? – Lambent

"UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members." from 'Guidelines for defining and using recursive common table expressions' section in learn.microsoft.com/en-us/sql/t-sql/queries/… – Tamratamsky

Thank's very much for this answer. I also found this: #48977701 explaining it – Lambent

Answer 4

If you have a date/time column, then use the month() function:

select t.*
from t
where month(datecol) = 5;

Curt

Nope, I have Date as Data Type in table. and above query returns date those are existing in my table. – Killoran

I think the issue here is to make the date table – Homy

Answer 5

You can get all the dates of a month using the following query.

declare @month int, @year int
set @month = 2
set @year = 2008

SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number
FROM master..spt_values
WHERE type = 'P'
AND
(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )

Hope this will help you.

Rearward

This code returned all days of January (!) (with variable @month = 2) and one day of February in the year 2008 – Lambent

Answer 6

This is a other ways but same results.

declare @year int=2021,@month int=3

select  DATEADD(dd,a.n-1,datefromparts(@year,@month,1)) 
from    (
         select top 31 ROW_NUMBER() over (order by a.object_id) as n
         from   sys.all_objects a
      ) a where DATEPART(mm,DATEADD(dd,a.n-1,datefromparts(@year,@month,1)))=3

Carlettacarley

Answer 7

Little modification. Query given by @t-clausen.dk will give you correct result only if you ran it on first of the month. With little change this works awesome.

DECLARE @date DATE = getdate()

;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 top(day(EOMONTH(@date)))
 N day,
 DATEFROMPARTS(year(@date),month(@date), n) date
FROM tally

BTW nice trick t-clausen.dk. I couldn't think of more easy way

Depurate

Answer 8

DECLARE @MonthNo TINYINT = 03 -- set your month
    ,@WOYEAR SMALLINT = 2018; -- set your year

IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL
    DROP TABLE #TMP

DECLARE @START_DATE DATETIME
    ,@END_DATE DATETIME
    ,@CUR_DATE DATETIME

SET @START_DATE = DATEADD(month, @MonthNo - 1, DATEADD(year, @WOYEAR - 1900, 0))
SET @END_DATE = DATEADD(day, - 1, DATEADD(month, @MonthNo, DATEADD(year, @WOYEAR - 1900, 0)))
SET @CUR_DATE = @START_DATE

CREATE TABLE #TMP (
    WEEKDAY VARCHAR(10)
    ,DATE INT
    ,MONTH VARCHAR(10)
    ,YEAR INT
    ,dates VARCHAR(25)
    )

WHILE @CUR_DATE <= @END_DATE
BEGIN
    INSERT INTO #TMP
    SELECT DATENAME(DW, @CUR_DATE)
        ,DATEPART(DAY, @CUR_DATE)
        ,DATENAME(MONTH, @CUR_DATE)
        ,DATEPART(YEAR, @CUR_DATE)
        ,REPLACE(CONVERT(VARCHAR(9), @CUR_DATE, 6), ' ', '-')

    SET @CUR_DATE = DATEADD(DD, 1, @CUR_DATE)
END

SELECT *
FROM #TMP

Stat

Answer 9

DECLARE @Today DATE= GETDATE() ,
@StartOfMonth DATE ,
@EndOfMonth DATE;

DECLARE @DateList TABLE ( DateLabel VARCHAR(10) );
SET @EndOfMonth = EOMONTH(GETDATE());
SET @StartOfMonth = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1);

WHILE @StartOfMonth <= @EndOfMonth
BEGIN
   INSERT  INTO @DateList
   VALUES  ( @StartOfMonth );
   SET @StartOfMonth = DATEADD(DAY, 1, @StartOfMonth);
END;

SELECT  DateLabel
FROM    @DateList; 

Vanadinite

Answer 10

WHERE Dates LIKE '2018-12%'

In a datetime or timestamp it goes Year-Month, so this would pull everything that matches 2018 in December. You can mod this to use your variables as well.

@month = 12;
@year = 2018;
@searchQuery = @year + @month + '%';

WHERE Dates LIKE @searchQuery

Peacock

Answer 11

Here is a query:

DECLARE @ReportDate VARCHAR(20)='2019-02-10'

DECLARE @LastDay INT =DAY(EOMONTH(@ReportDate))

DECLARE @DayStart INT=01

CREATE TABLE #TEMPMonth ([MDay] VARCHAR(20))

WHILE @LastDay>=@DayStart

BEGIN
    
INSERT INTO #TEMPMonth 
    
SELECT CAST(CAST(YEAR(@ReportDate)AS VARCHAR)+'-'+CAST(MONTH(@ReportDate)AS VARCHAR)+'-'+CAST(@DayStart AS VARCHAR) AS DATE)
    SET @DayStart+=1

END

SELECT * FROM #TEMPMonth

DROP TABLE #TEMPMonth

Mcgaha

Answer 12

CREATE FUNCTION fn_GetMonthlyDates
(
  @ProcessDate smalldatetime
)
RETURNS @LOAN TABLE 
(
 ProcessDate    smalldatetime
)
AS
BEGIN
    DECLARE 
        @Today DATE= @ProcessDate,
        @StartOfMonth DATE ,
        @EndOfMonth DATE;

    DECLARE @DateList TABLE (DateLabel VARCHAR(10) );
    SET @EndOfMonth = EOMONTH(@Today);
    SET @StartOfMonth = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1);

    WHILE @StartOfMonth <= @EndOfMonth
    BEGIN
       INSERT  INTO @DateList
       VALUES  (@StartOfMonth );
       SET @StartOfMonth = DATEADD(DAY, 1, @StartOfMonth);
    END;

    INSERT INTO @LOAN(ProcessDate)
    SELECT  DateLabel
    FROM    @DateList; 

RETURN
END

Pothook

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Unfrequented

Answer 13

A date list table won't take up my storage, will be more efficient than regularly run queries/functions and can be utilised elsewhere.

Create table and populate with all the dates you will need for the application's lifetime:

CREATE TABLE [dbo].[DateTable](
  [Date] [smalldatetime] NOT NULL PRIMARY KEY
, [Year] as year([Date]) PERSISTED NOT NULL
, [Month] as month([Date]) PERSISTED NOT NULL
, [Day] as day([Date]) PERSISTED NOT NULL);

Populate the table:

WITH DT
AS
(
  SELECT CAST('20000101' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM DT
  WHERE DATEADD(dd, 1, [date]) <= '20501231'
)
INSERT INTO DateTable
SELECT [date] FROM DT
OPTION (MAXRECURSION 0);

Non-Clustered Index:

CREATE NONCLUSTERED INDEX [IX_DateTable_Year_Month_inc_Date] ON [dbo].[DateTable]
(
    [Year] ASC,
    [Month] ASC
)
INCLUDE([Date])

Use will be simple and efficient from the non-clustered index:

select [Date] 
from DateTable 
where [Year] = @year and [Month] = @Month

Cockchafer

Answer 14

I think the easier way to do that is something like this.

 Declare @date datetime ='20240301'
SELECT CONVERT(DATE, right('0'+convert(varchar,dia),2) +'/'+Right('0'+convert(varchar,Month(@date)),2)+'/'+convert(varchar,year(@date)),103)
    FROM (select value as dia from String_split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,',',')) T0
    WHERE T0.dia <= day(EOMONTH(@date))
    and isnull(dia,'')!=''

Lachrymatory

Answer 15

Use generate_series. For example:

SELECT * FROM generate_series('2012-10-01':: date,'2012-10-31','1 day'); 

Coldshoulder

Answer 16

-- Another simple version:

DECLARE @ADate DATETIME = '2022-03-01'

DECLARE @Numberofdays INT = (SELECT DAY(EOMONTH(@ADate))) , @i int = 0

while @i < @Numberofdays begin print CONVERT(varchar(8), dateadd(day, @i, @ADate ), 112) set @i= @i+1 end

Pipestone