CODEWITHSUNDEEP
sql-serversql-server-2012
Pedram
Pedram

Reputation: 6508

Get All Dates of Given Month and Year in SQL Server

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

Upvotes: 17

Views: 71372

Answers (16)

rschwartz
rschwartz

Reputation: 1

Use generate_series. For example:

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

Upvotes: 0

Cristian Lopez
Cristian Lopez

Reputation: 1

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

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

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

Upvotes: 0

Mansour
Mansour

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

Upvotes: -1

Nguyen Son Tung
Nguyen Son Tung

Reputation: 331

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

Upvotes: 1

J.Shiundu
J.Shiundu

Reputation: 1

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

Upvotes: 0

Sameer Bhambalkar
Sameer Bhambalkar

Reputation: 1

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

Upvotes: 0

Amit007
Amit007

Reputation: 27

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

Upvotes: 0

StuKay
StuKay

Reputation: 317

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

Upvotes: 3

vega480
vega480

Reputation: 21

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

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5643

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.

Upvotes: 1

Sameer
Sameer

Reputation: 381

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;

Upvotes: 0

Sagar Mahajan
Sagar Mahajan

Reputation: 191

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

Upvotes: 0

Gerrit Horeis
Gerrit Horeis

Reputation: 561

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

Upvotes: 15

t-clausen.dk
t-clausen.dk

Reputation: 44326

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

Upvotes: 12

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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

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

Upvotes: 2

Related Questions