-2

I need to write a query which returns No. of days of a Month. Please understand that I cannot use DATE, and any query having DATE will not work.

I have created a parameter which takes int value. Like January is 1, February is 2 etc.

From the values 1 or 2, I wanted to get the number of days like the number of days of 1 should be 31 and 2 should be 28 or 29.

I am using Ms sql-server-2008.

Help is required.

Regards,

JamshaidRiaz
  • 111
  • 7
  • Check out this link: https://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server – Timothé Malahieude Aug 10 '17 at 08:01
  • 2
    Possible duplicate of [How to determine the number of days in a month in SQL Server?](https://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server) – deblocker Aug 10 '17 at 08:03
  • Looks like we found the same thing! – Timothé Malahieude Aug 10 '17 at 08:04
  • See my question is to get the total number of days from a int value, no DATE is mention here. and I cannot use Date. – JamshaidRiaz Aug 10 '17 at 08:24
  • 1
    Why can't you use date? You can build a date value using the month number and just use datediff to determine the number of days, what is wrong with this solution? – Pred Aug 10 '17 at 08:36
  • its demand of the customer to enter the year and month in separate Fields and he asked to enter in numbers. – JamshaidRiaz Aug 10 '17 at 08:43
  • I do understand, but you can build a date using that information and use that calculated value to determine the length of the month – Pred Aug 10 '17 at 08:44
  • yes, Thanks alot. that is a great help. I didn't even think about it. – JamshaidRiaz Aug 10 '17 at 08:52

1 Answers1

1

So, you can always build a date using date parts. You may want to validate the input and transform this logic to your needs.

DECLARE @year SMALLINT = 2017;
DECLARE @month TINYINT = 2;

DECLARE @dateFrom DATE = CONVERT(DATE, 
      CONVERT(CHAR(4), @year) + '-'
    + RIGHT('00' + CONVERT(VARCHAR(2), @month), 2)
    + '-01'
);

DECLARE @dateTo DATE = DATEADD(MONTH, 1, @dateFrom);

SELECT DATEDIFF(DAY, @dateFrom, @dateTo);

-- OR --

If you are really keen to not use any calculation, just a query, store these values in a table and query that table using the input values.

Pred
  • 8,789
  • 3
  • 26
  • 46