how to get current month full days in sql server?

Question

Hi guys.

I want to get current date from system and return 30-31 days of current month

foreaxmple : today is 12/7/2023

query should return :

12/01/2023

12/02/2023

...

12/30/2023

and show 30 days for 30days month 29 for 29 month and ...

Answer 1

Hi @Farshad Valizade

Try this query:

;WITH DateTable AS
(
 SELECT  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
)
 SELECT CAST([date] AS date) DaysOfMonth
 FROM DateTable 

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Farshad Valizade

If SQL Server 2017+ then use STRING_AGG like this:

;WITH DateTable AS
(
 SELECT  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
)
SELECT STRING_AGG(CAST([date] AS date), ' | ') WITHIN GROUP (ORDER BY [date] ASC) AS DaysOfMonth
FROM DateTable

If versions before 2017, check this query:

;WITH DateTable AS
(
 SELECT  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
)
SELECT STUFF((SELECT ' | ' + CONVERT(CHAR(10),[date],111) FROM DateTable ORDER BY [date]
              FOR XML PATH('')),1,2,'') AS DaysOfMonth