How to determine the number of days in a month in SQL Server

Question

I need to determine the number of days in a month for a given date in SQL Server   Is there a built-in function  If not  what should I use as the user-defined function

User · Answer

DECLARE  date nvarchar 20  SET  date   2012-02-09 00 00 00  SELECT DATEDIFF day cast replace cast YEAR  date  as char   -  cast MONTH  date  as char   -01            00 00 00  as datetime  dateadd month 1 cast replace cast YEAR  date  as char   -  cast MONTH  date  as char   -01            00 00 00  as datetime

User · Answer

In SQL Server 2012 you can use EOMONTH  Transact-SQL  to get the last day of the month and then you can use DAY  Transact-SQL  to get the number of days in the month   DECLARE  ADate DATETIME  SET  ADate   GETDATE    SELECT DAY EOMONTH  ADate   AS DaysInMonth

User · Answer

I upvoted Mehrdad  but this works as well      CREATE function dbo IsLeapYear        TestYear int   RETURNS bit AS BEGIN     declare  Result bit     set  Result        cast          case when    TestYear   4   0  and   testYear   100    0   or   TestYear   400   0          then 1         else 0         end     as bit       return  Result END GO  CREATE FUNCTION dbo GetDaysInMonth        TestDT datetime   RETURNS INT AS BEGIN      DECLARE  Result int      DECLARE  MonthNo int      Set  MonthNo   datepart m  TestDT       Set  Result        case  MonthNo         when  1 then 31         when  2 then              case                  when dbo IsLeapYear datepart yyyy  TestDT     0                 then 28                 else 29             end         when  3 then 31         when  4 then 30         when  5 then 31         when  6 then 30         when  7 then 31         when  8 then 31         when  9 then 30          when 10 then 31         when 11 then 30          when 12 then 31     end      RETURN  Result END GO   To Test  declare  testDT datetime   set  testDT    2404-feb-15    select dbo GetDaysInMonth  testDT

User · Answer

This code gets you the number of days in current month   SELECT datediff dd getdate   dateadd mm 1 getdate     as datas   Change getdate   to the date you need to count days for

User · Answer

select first day dateadd dd -1 datepart dd getdate    1 getdate      last day dateadd dd -1 datepart dd dateadd mm 1 getdate     dateadd mm 1 getdate      no of days   1 datediff dd dateadd dd -1 datepart dd getdate    1 getdate    dateadd dd -1 datepart dd dateadd mm 1 getdate     dateadd mm 1 getdate       replace any date with getdate to get the no of months in that particular date

User · Answer

simple query in SQLServer2012    select day   20-05-1951 22 00 00     i tested for many dates and it return always a correct result

User · Answer

SELECT DAY SUBDATE ADDDATE CONCAT YEAR NOW      -   MONTH NOW      -1    INTERVAL 1 MONTH   INTERVAL 1 DAY     Nice  n  Simple and does not require creating any functions

User · Answer

You can use the following with the first day of the specified month   datediff day   date  dateadd month  1   date     To make it work for every date   datediff day  dateadd day  1-day  date    date                 dateadd month  1  dateadd day  1-day  date    date

User · Answer

I would suggest   SELECT DAY EOMONTH GETDATE

User · Answer

--Last Day of Previous Month SELECT DATEPART day  DATEADD s -1 DATEADD mm  DATEDIFF m 0 GETDATE    0     --Last Day of Current Month SELECT DATEPART day  DATEADD s -1 DATEADD mm  DATEDIFF m 0 GETDATE    1 0     --Last Day of Next Month SELECT DATEPART day  DATEADD s -1 DATEADD mm  DATEDIFF m 0 GETDATE    2 0      Personally though  I would make a UDF for it if there is not a built in function

User · Answer

You need to create a function  but it is for your own convenience  It works perfect and I never encountered any faulty computations using this function     CREATE FUNCTION  dbo   get days   date datetime  RETURNS int AS BEGIN     SET  date   DATEADD MONTH  1   date      DECLARE  result int    select DAY DATEADD DAY  -DAY  date    date        RETURN  result END   How it works  subtracting the date s day number from the date itself gives you the last day of previous month  So  you need to add one month to the given date  subtract the day number and get the day component of the result

User · Answer

For any date  select DateDiff Day  date DateAdd month 1  date

User · Answer

DECLARE   m int SET      m   2  SELECT          m AS  Month          DATEDIFF DAY                  DATEADD DAY  0  DATEADD m    m -1  0                    DATEADD DAY  0  DATEADD m    m  0                    AS  Days in Month

User · Answer

--- sql server below 2012---     select day  dateadd day -1 dateadd month  1  convert date  2019-03-01          -- this for sql server 2012--     select day EOMONTH getdate

User · Answer

RETURN day dateadd month  12    year    month - 22800  -1    select day dateadd month  12   year date    month date  - 22800  -1

User · Answer

To get the no  of days in a month we can directly use Day   available in SQL   Follow the link posted at the end of my answer for SQL Server 2005   2008   The following example and the result are from SQL 2012  alter function dbo  daysinm     dates nvarchar 12    returns int as begin Declare  dates2 nvarchar 12  Declare  days int begin select  dates2    select DAY EOMONTH convert datetime  dates 103     set  days   convert int  dates2  end return  days end  --select dbo daysinm  08 12 2016     Result in SQL Server SSMS     no column name  1 31   Process   When EOMONTH is used  whichever the date format we use it is converted into DateTime format of SQL-server  Then the date output of EOMONTH   will be 2016-12-31 having 2016 as Year  12 as Month and 31 as Days  This output when passed into Day   it gives you the total days count in the month   If we want to get the instant result for checking we can directly run the below code   select DAY EOMONTH convert datetime  08 12 2016  103      or  select DAY EOMONTH convert datetime getdate   103      for reference to work in SQL Server 2005 2008 2012  please follow the following external link      Find No  of Days in a Month in SQL

User · Answer

Solution 1  Find the number of days in whatever month we re currently in  DECLARE  dt datetime SET      dt   getdate    SELECT  dt AS  DateTime          DAY DATEADD mm  DATEDIFF mm  -1   dt   -1   AS  Days in Month    Solution 2  Find the number of days in a given month-year combo  DECLARE  y int   m int SET      y   2012 SET      m   2  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

User · Answer

You do need to add a function  but it s a simple one   I use this   CREATE FUNCTION  dbo   ufn GetDaysInMonth     pDate    DATETIME    RETURNS INT AS BEGIN      SET  pDate   CONVERT VARCHAR 10    pDate  101      SET  pDate    pDate - DAY  pDate    1      RETURN DATEDIFF DD   pDate  DATEADD MM  1   pDate   END  GO

User · Answer

SELECT Datediff day   Convert DateTime Convert varchar 2  Month getdate       01   Convert varchar 4  Year getdate         Convert DateTime Convert varchar 2  Month getdate    1    01   Convert varchar 4  Year getdate        as  No of Days in a Month

User · Answer

Most elegant solution  works for any  DATE  DAY DATEADD DD -1 DATEADD MM DATEDIFF MM -1  DATE  0      Throw it in a function or just use it inline  This answers the original question without all the extra junk in the other answers   examples for dates from other answers   SELECT DAY DATEADD DD -1 DATEADD MM DATEDIFF MM -1  1 31 2009   0    Returns 31  SELECT DAY DATEADD DD -1 DATEADD MM DATEDIFF MM -1  2404-feb-15   0    Returns 29  SELECT DAY DATEADD DD -1 DATEADD MM DATEDIFF MM -1  2011-12-22   0    Returns 31

User · Answer

select add months trunc sysdate  MM   1  -  trunc sysdate  MM   from dual

User · Answer

I know this question is old but I thought I would share what I m using   DECLARE  date date    2011-12-22      FindFirstDayOfMonth - Find the first date of any month    -- Replace the day part with -01 DECLARE  firstDayOfMonth date   CAST  CAST YEAR  date  AS varchar 4      -                                           CAST MONTH  date  AS varchar 2      -01  AS date  SELECT  firstDayOfMonth   and  DECLARE  date date    2011-12-22      FindLastDayOfMonth - Find what is the last day of a month - Leap year is handled by DATEADD    -- Get the first day of next month and remove a day from it using DATEADD DECLARE  lastDayOfMonth date   CAST  DATEADD dd  -1  DATEADD mm  1  FindFirstDayOfMonth  date    AS date   SELECT  lastDayOfMonth   Those could be combine to create a single function to retrieve the number of days in a month if needed

User · Answer

Much simpler   try day eomonth  Date

User · Answer

Mehrdad Afshari reply is most accurate one  apart from usual this answer is based on formal mathematical approach given by Curtis McEnroe in his blog https   cmcenroe me 2014 12 05 days-in-month-formula html   DECLARE  date  DATE   2015-02-01  DECLARE  monthNumber TINYINT  DECLARE  dayCount TINYINT SET  monthNumber   DATEPART MONTH  date   SET  dayCount   28     monthNumber   floor  monthNumber 8     2   2       monthNumber   2   floor 1  monthNumber     SELECT  dayCount   CASE WHEN  dayCount   28 AND DATEPART YEAR  date  4  0 THEN 1 ELSE 0 END -- leap year adjustment

User · Answer

select  datediff day           dateadd day  0  dateadd month    2013 - 1900    12    3 - 1  0            dateadd day  0  dateadd month    2013  - 1900    12    3  0               Nice  Simple and does not require creating any functions Work Fine

User · Answer

DECLARE  date DATETIME   GETDATE    --or  12 1 2018   month day year   SELECT DAY EOMONTH    date    AS  This Month    SELECT DAY EOMONTH    date  1    AS  Next Month     result  This Month 31  Next Month 30

User · Answer

DECLARE  Month INT 2       Year INT 1989 DECLARE  date DateTime null SET  date CAST CAST  Year AS nvarchar     -    CAST  Month AS nvarchar     -     1  AS DATETIME    DECLARE  noofDays TINYINT  DECLARE  CountForDate TINYINT SET  noofDays   DATEPART MONTH  date   SET  CountForDate   28     noofDays   floor  noofDays 8     2   2       noofDays   2   floor 1  noofDays     SET  noofDays   CountForDate   CASE WHEN  CountForDate   28 AND DATEPART YEAR  date  4  0 THEN 1 ELSE 0 END PRINT  noofDays

User · Answer

here s another one       Select Day DateAdd day  -Day DateAdd month  1  getdate                                DateAdd month  1  getdate

[sql] How to determine the number of days in a month in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to datetime

Examples related to date

Examples related to user-defined-functions