how to get each month days of particular year in sql

Question

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

Jayesh Vaghasiya · 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

Pravat Kumar · Answer

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

Yogesh Ghorpade · Answer

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]

Balaji KN · Answer

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)

how to get each month days of particular year in sql

Answers (4)