How to get number of days from month and year

Question

I want to get the number of days in the month which the user specifies. I am using this it works for most months except for Feb and leap year. It shows 28 days and not 29. Can you solve this?

begin
declare @year int
declare @month int
select @year = 2012
select @month = DATEPART(mm,CAST('August'+ ' 2012' AS DATETIME))
select  datediff(day, 
        dateadd(day, 0, dateadd(month, ((@year - 2012) * 12) + @month - 1, 0)),
        dateadd(day, 0, dateadd(month, ((@year - 2012) * 12) + @month, 0))) as number_of_days

end

Or If not can you tell me another approach to do this. It should use @year and @month and the code to find the days can be any!

There are a lot of solutions in the [answers here](http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server) — kwwallpe, Jun 26 '13 at 11:23
Yes man I saw that post but in that post. Everyone has used getdate(), or they are inputting the date. I want to input the year and month separately !! — Ashish Batra, Jun 26 '13 at 11:27

score 3 · Answer 1 · answered Jun 26 '13 at 11:50

If you need to do this from year and month (assuming both are integers) you could create a function as so:

CREATE FUNCTION dbo.DaysInMonth (@year INT, @Month INT)
RETURNS INT 
AS
BEGIN
    -- FIRST CONVERT THE YEAR AND MONTH TO A DATE BY CASTING TO CHAR
    -- THEN CONCATENATING TO CREATE A STRING IN THE FORMAT yyyyMMdd
    -- THIS DATEFORMAT IS CULTURE INSENSITIVE SO WILL WORK NO MATTER
    -- WHAT YOUR REGIONAL SETTINGS ARE

    DECLARE @Date DATE = CAST(
                            CAST(@Year AS CHAR(4)) 
                            + RIGHT('0' + CAST(@Month AS VARCHAR(2)), 2)
                            + '01' AS DATE);

    -- USE ESTABLISHED METHODS OF GETTING 1ST OF THE MONTH AND FIRST OF 
    -- THE NEXT MONTH AND CALCULATE THE DIFFERENCE
    RETURN DATEDIFF(DAY, 
            DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0),
            DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0));
END
GO
-- TEST FUNCTION
SELECT  DaysInMonth = dbo.DaysInMonth(2012, 2);

Example on SQL Fiddle

score 2 · Answer 2 · answered Oct 22 '14 at 11:14

2

This would be a good solution.

DECLARE @year INT,@month INT

SET @year = 2011
SET @month = 2

SELECT DAY(EOMONTH(DATEFROMPARTS(@year,@month,1)))

answered Oct 22 '14 at 11:14

Jithin Shaji

5,893
5
25
47

score 1 · Answer 3 · answered May 08 '15 at 09:48

Gareth solution modified for SQL Server 2005

CREATE FUNCTION dbo.DaysInMonth (@year INT, @Month INT)
RETURNS INT 
AS
BEGIN
    -- FIRST CONVERT THE YEAR AND MONTH TO A DATE BY CASTING TO CHAR
    -- THEN CONCATENATING TO CREATE A STRING IN THE FORMAT yyyyMMdd
    -- THIS DATEFORMAT IS CULTURE INSENSITIVE SO WILL WORK NO MATTER
    -- WHAT YOUR REGIONAL SETTINGS ARE

    DECLARE @Date datetime
    SET @DATE = CAST(
                            CAST(@Year AS CHAR(4)) 
                            + RIGHT('0' + CAST(@Month AS VARCHAR(2)), 2)
                            + '01' AS DATETIME);

    -- USE ESTABLISHED METHODS OF GETTING 1ST OF THE MONTH AND FIRST OF 
    -- THE NEXT MONTH AND CALCULATE THE DIFFERENCE
    RETURN DATEDIFF(DAY, 
            DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0),
            DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0));
END
GO

score 0 · Answer 4 · edited May 23 '17 at 12:32

0

Then all you need to do is take your desired inputs, cast them to a date stored in @date, and you can use the first example from that post without any changes. Using your code as-is, it only takes one more line of casting and then the first solution:

declare @year int
declare @month int
declare @date date
select @year = 2012
select @month = DATEPART(mm,CAST('august'+ ' 2012' AS DATETIME))
select @date = cast(cast(@month as varchar(20)) + '/1/' + cast(@year as varchar(4)) as datetime)

select @month, datediff(day, dateadd(day, 1-day(@date), @date),
          dateadd(month, 1, dateadd(day, 1-day(@date), @date)))

edited May 23 '17 at 12:32

Community

1
1

answered Jun 26 '13 at 11:40

kwwallpe

129
3

This would fail on anything with a date format setting of `DMY`. You need to explicitly state `SET DATEFORMAT MDY;` if you are going to use this format. Better still use the culture insensitive dateformat yyyyMMdd. – GarethD Jun 26 '13 at 11:43

How to get number of days from month and year

4 Answers4

Linked