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

You'll want to create a date table in your database. You'll then be able to link using the BETWEEN function. — Rich Benner, May 04 '16 at 12:27
"This question does not show any research effort". What have you tried before asking here? — Rich Benner, May 04 '16 at 12:32
I'm not a down voter myself but I suspect the reason is this has been [asked before](http://stackoverflow.com/search?q=days+in+month+%5Bsql-server%5D). You can use a calendar/date table, tally table or recursive CTE. Of those I'd recommend a [date table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server). These are so handy, especially in reporting. — David Rushton, May 04 '16 at 12:39

score 15 · Answer 1 · edited Feb 20 '18 at 08:34

15

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

edited Feb 20 '18 at 08:34

t-clausen.dk

43,517
12
59
92

answered Nov 02 '17 at 10:08

Gerrit Horeis

541
5
14

t-clausen.dk · Accepted Answer · 2016-05-04T12:57:51.437

12

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)))

edited May 04 '16 at 12:57

answered May 04 '16 at 12:27

t-clausen.dk

43,517
12
59
92

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. – Pedram May 04 '16 at 12:36
1

@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 – t-clausen.dk May 04 '16 at 12:47
now it returns 30 rows :( it should return 31 rows :) – Pedram May 04 '16 at 12:49
1

fixed now. Changed N< to N<= – t-clausen.dk May 04 '16 at 12:50
Query is near unreadable and provides no explanation as to what you did to achieve the solution. – Bitz Dec 28 '20 at 19:19

score 3 · Answer 3 · answered Feb 07 '19 at 18:10

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

answered Feb 07 '19 at 18:10

StuKay

307
1
7

Very Nice! This worked perfectly for me. – Michael W. Jan 09 '23 at 08:59
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? – Michael W. Jan 09 '23 at 09:35
1

"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 https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16 – StuKay Jan 10 '23 at 11:00
Thank's very much for this answer. I also found this: https://stackoverflow.com/questions/48977201/why-does-a-recursive-cte-not-return-duplicates explaining it – Michael W. Jan 10 '23 at 15:36

score 2 · Answer 4 · answered May 04 '16 at 12:23

2

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

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

answered May 04 '16 at 12:23

Gordon Linoff

1,242,037
58
646
786

Nope, I have Date as Data Type in table. and above query returns date those are existing in my table. – Pedram May 04 '16 at 12:27
I think the issue here is to make the date table – sagi May 04 '16 at 12:31

score 1 · Answer 5 · answered Nov 05 '18 at 10:34

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.

This code returned all days of January (!) (with variable @month = 2) and one day of February in the year 2008 — Michael W., Jan 09 '23 at 08:40

Nguyen Son Tung · Answer 6 · 2021-05-27T02:53:49.577

1

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

edited May 27 '21 at 02:53

answered May 27 '21 at 02:41

Nguyen Son Tung

331
3
5

Sagar Mahajan · Answer 7 · 2018-04-06T11:52:11.717

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

score 0 · Answer 8 · answered May 21 '18 at 08:58

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;

score 0 · Answer 9 · answered Jan 07 '19 at 23:26

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

Amit007 · Answer 10 · 2020-06-28T14:25:31.123

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

score 0 · Answer 11 · edited Oct 23 '20 at 09:19

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

score 0 · Answer 12 · edited Jan 06 '21 at 12:22

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

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. — Tyler2P, Jan 06 '21 at 12:24

cloudsafe · Answer 13 · 2022-03-29T10:04:32.050

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

score 0 · Answer 14 · edited May 14 '22 at 04:39

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,'')!=''

score 0 · Answer 15 · answered Mar 26 '23 at 16:57

0

Use generate_series. For example:

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

answered Mar 26 '23 at 16:57

rschwartz

1
1

score -1 · Answer 16 · answered Mar 29 '22 at 08:11

-1

-- 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

answered Mar 29 '22 at 08:11

Mansour

1

Get All Dates of Given Month and Year in SQL Server

16 Answers16

Linked