how to get each month days of particular year in sql

venkat

8y961

Hi all,

Could you please help on below requirement?

i need to diplay each month days in sql.

my output sholud like this

Example: Jan 31

feb 28

mar 31

Dec 31

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayesh Vaghasiya8y ago
Accepted answer
DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = '2010-01-14',@EndDate = '2010-12-01';
WITH
cteDates AS
(
SELECT TOP (DATEDIFF(mm,@StartDate,@EndDate) + 1)MonthDate = DATEADD(mm,DATEDIFF(mm,0,@StartDate) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT LEFT(DATENAME(month, MonthDate), 3) + ' ' + CONVERT(VARCHAR(2), DATEDIFF(DD, MonthDate, DATEADD(MM, 1, MonthDate))) FROM cteDates

Output

EachMonthDays
Jan 31
Feb 28
Mar 31
Apr 30
May 31
Jun 30
Jul 31
Aug 31
Sep 30
Oct 31
Nov 30
Dec 31

+1
Pravat Kumar8y ago
Hi Venkat,

please execute below query once.

DECLARE @pDate DATETIME
SET @pDate = GETDATE()
SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)
SET @pDate = @pDate - DAY(@pDate) + 1
SELECT LEFT(DATENAME(month, @pDate), 3) + ' ' + CONVERT(VARCHAR(2), DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate)))
0
Yogesh Ghorpade8y ago
DECLARE @y int, @m int
SET @y = 2018
SET @m = 8
SELECT @y AS [Year],
@m AS [Month],
DATEDIFF(DAY,
DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m - 1, 0)),
DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m, 0))
) AS [Days in Month]
0
Balaji KN8y ago
Add SQL function that takes int as parameter . In that function have case statement depending on month number returns number of days in that month ( for month number 2 need to have some more condition)
-1