How to Find the Total Number of Used Days in a Month

How do I find the total number of used days in a month?

Test Data:

CREATE TABLE your_table ( usr, start_date, end_date ) AS (
  SELECT 'A', DATE '2017-06-01', DATE '2017-06-03' FROM DUAL UNION ALL
  SELECT 'B', DATE '2017-06-02', DATE '2017-06-04' FROM DUAL UNION ALL -- Overlaps previous
  SELECT 'C', DATE '2017-06-06', DATE '2017-06-06' FROM DUAL UNION ALL
  SELECT 'D', DATE '2017-06-07', DATE '2017-06-07' FROM DUAL UNION ALL -- Adjacent to previous
  SELECT 'E', DATE '2017-06-11', DATE '2017-06-20' FROM DUAL UNION ALL
  SELECT 'F', DATE '2017-06-14', DATE '2017-06-15' FROM DUAL UNION ALL -- Within previous
  SELECT 'G', DATE '2017-06-22', DATE '2017-06-25' FROM DUAL UNION ALL
  SELECT 'H', DATE '2017-06-24', DATE '2017-06-28' FROM DUAL UNION ALL -- Overlaps previous and next
  SELECT 'I', DATE '2017-06-27', DATE '2017-06-30' FROM DUAL UNION ALL
  SELECT 'J', DATE '2017-06-27', DATE '2017-06-28' FROM DUAL;          -- Within H and I          

Query:

SELECT SUM( days ) AS total_days
FROM   (
  SELECT dt - LAG( dt ) OVER ( ORDER BY dt ) + 1 AS days,
         start_end
  FROM   (
    SELECT dt,
           CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
             WHEN 1 THEN 'start'
             WHEN 0 THEN 'end'
           END AS start_end
    FROM   your_table
    UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
  )
  WHERE start_end IS NOT NULL
)
WHERE start_end = 'end';

Output:

TOTAL_DAYS
----------
        25

Explanation:

SELECT dt, value
FROM   your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )

This will UNPIVOT the table so that the start and end dates are in the same column (dt) and are given a corresponding value of +1 for a start and -1 for an end date.

SELECT dt,
       SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) AS total,
       value
FROM   your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )

Will give the start and end dates and the cumulative sum of those generated values. The start of a range will always have value=1 and total=1 and the end of a range will always have total=0. If a date is mid-way through a range then it will either have total>1 or value=-1 and total=1. Using this, if you multiply value and total then the start of a range is when value*total=1 and the end of a range is when value*total=0 and any other value indicates a date that is midway through a range.

Which is what this gives:

SELECT dt,
       CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
         WHEN 1 THEN 'start'
         WHEN 0 THEN 'end'
       END AS start_end
FROM   your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )

You can then filter out the dates when the start_end is NULL which will leave you with a table with alternating start and end rows which you can use LAG to calculate the number of days difference:

SELECT dt - LAG( dt ) OVER ( ORDER BY dt ) + 1 AS days,
       start_end
FROM   (
  SELECT dt,
         CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
           WHEN 1 THEN 'start'
           WHEN 0 THEN 'end'
         END AS start_end
  FROM   your_table
  UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
)
WHERE start_end IS NOT NULL

All you need to do then is to SUM all the differences for the end - start; which gives the query above.

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

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

Universal formula to calculate the number of days in a month taking into account leap years

It isn't very hard to add a term (m == 2) * leapyear(yyyy) to the expression to determine the correct number of days in February of a leap year. This C code shows a way to do it:

#include <stdio.h>
#include <stdbool.h>

static inline bool leapyear(int yy)
{
    if (yy %   4 != 0) return false;
    if (yy % 100 != 0) return true;
    if (yy % 400 != 0) return false;
    return true;
}

static inline int old_dim(int mm)
{
    return (28 + (mm + (mm/8)) % 2 + 2 % mm + 2 * (1/mm));
}

static inline int new_dim(int mm, int yyyy)
{
    return (28 + (mm + (mm/8)) % 2 + 2 % mm + 2 * (1/mm) + ((mm == 2) * leapyear(yyyy)));
}

int main(void)
{
    /*28 + (x + Math.floor(x/8)) % 2 + 2 % x + 2 * Math.floor(1/x);*/
    for (int mm = 1; mm <= 12; mm++)
        printf("mm = %2d, DIM = %2d\n", mm, old_dim(mm));

    for (int yyyy = 1900; yyyy < 2101; yyyy += 5)
    {
        for (int mm = 1; mm <= 12; mm++)
            printf("yyyy = %4d, mm = %2d: DIM = %2d\n", yyyy, mm, new_dim(mm, yyyy));
    }

    return 0;
}

The output for mm = 2 (filtered from the full output) is:

yyyy = 1900, mm =  2: DIM = 28
yyyy = 1905, mm =  2: DIM = 28
yyyy = 1910, mm =  2: DIM = 28
yyyy = 1915, mm =  2: DIM = 28
yyyy = 1920, mm =  2: DIM = 29
yyyy = 1925, mm =  2: DIM = 28
yyyy = 1930, mm =  2: DIM = 28
yyyy = 1935, mm =  2: DIM = 28
yyyy = 1940, mm =  2: DIM = 29
yyyy = 1945, mm =  2: DIM = 28
yyyy = 1950, mm =  2: DIM = 28
yyyy = 1955, mm =  2: DIM = 28
yyyy = 1960, mm =  2: DIM = 29
yyyy = 1965, mm =  2: DIM = 28
yyyy = 1970, mm =  2: DIM = 28
yyyy = 1975, mm =  2: DIM = 28
yyyy = 1980, mm =  2: DIM = 29
yyyy = 1985, mm =  2: DIM = 28
yyyy = 1990, mm =  2: DIM = 28
yyyy = 1995, mm =  2: DIM = 28
yyyy = 2000, mm =  2: DIM = 29
yyyy = 2005, mm =  2: DIM = 28
yyyy = 2010, mm =  2: DIM = 28
yyyy = 2015, mm =  2: DIM = 28
yyyy = 2020, mm =  2: DIM = 29
yyyy = 2025, mm =  2: DIM = 28
yyyy = 2030, mm =  2: DIM = 28
yyyy = 2035, mm =  2: DIM = 28
yyyy = 2040, mm =  2: DIM = 29
yyyy = 2045, mm =  2: DIM = 28
yyyy = 2050, mm =  2: DIM = 28
yyyy = 2055, mm =  2: DIM = 28
yyyy = 2060, mm =  2: DIM = 29
yyyy = 2065, mm =  2: DIM = 28
yyyy = 2070, mm =  2: DIM = 28
yyyy = 2075, mm =  2: DIM = 28
yyyy = 2080, mm =  2: DIM = 29
yyyy = 2085, mm =  2: DIM = 28
yyyy = 2090, mm =  2: DIM = 28
yyyy = 2095, mm =  2: DIM = 28
yyyy = 2100, mm =  2: DIM = 28

This correctly considers 1900 and 2100 as non-leap years, but 2000 as a leap year.

yyyy = 1900, mm =  1: DIM = 31
yyyy = 1900, mm =  2: DIM = 28
yyyy = 1900, mm =  3: DIM = 31
yyyy = 1900, mm =  4: DIM = 30
yyyy = 1900, mm =  5: DIM = 31
yyyy = 1900, mm =  6: DIM = 30
yyyy = 1900, mm =  7: DIM = 31
yyyy = 1900, mm =  8: DIM = 31
yyyy = 1900, mm =  9: DIM = 30
yyyy = 1900, mm = 10: DIM = 31
yyyy = 1900, mm = 11: DIM = 30
yyyy = 1900, mm = 12: DIM = 31

…

yyyy = 2000, mm =  1: DIM = 31
yyyy = 2000, mm =  2: DIM = 29
yyyy = 2000, mm =  3: DIM = 31
yyyy = 2000, mm =  4: DIM = 30
yyyy = 2000, mm =  5: DIM = 31
yyyy = 2000, mm =  6: DIM = 30
yyyy = 2000, mm =  7: DIM = 31
yyyy = 2000, mm =  8: DIM = 31
yyyy = 2000, mm =  9: DIM = 30
yyyy = 2000, mm = 10: DIM = 31
yyyy = 2000, mm = 11: DIM = 30
yyyy = 2000, mm = 12: DIM = 31

Number of days in particular month of particular year?

Java 8 and later

@Warren M. Nocos.
If you are trying to use Java 8's new Date and Time API, you can use java.time.YearMonth class. See Oracle Tutorial.

// Get the number of days in that month
YearMonth yearMonthObject = YearMonth.of(1999, 2);
int daysInMonth = yearMonthObject.lengthOfMonth(); //28  

Test: try a month in a leap year:

yearMonthObject = YearMonth.of(2000, 2);
daysInMonth = yearMonthObject.lengthOfMonth(); //29 

Java 7 and earlier

Create a calendar, set year and month and use getActualMaximum

int iYear = 1999;
int iMonth = Calendar.FEBRUARY; // 1 (months begin with 0)
int iDay = 1;

// Create a calendar object and set year and month
Calendar mycal = new GregorianCalendar(iYear, iMonth, iDay);

// Get the number of days in that month
int daysInMonth = mycal.getActualMaximum(Calendar.DAY_OF_MONTH); // 28

Test: try a month in a leap year:

mycal = new GregorianCalendar(2000, Calendar.FEBRUARY, 1);
daysInMonth= mycal.getActualMaximum(Calendar.DAY_OF_MONTH);      // 29

Getting number of days in a month

You want DateTime.DaysInMonth:

int days = DateTime.DaysInMonth(year, month);

Obviously it varies by year, as sometimes February has 28 days and sometimes 29. You could always pick a particular year (leap or not) if you want to "fix" it to one value or other.

SQL Server query for total number of days for a month between date ranges

Ideally, you have a table named "Dates" with all the dates you will ever use, e.g. year 1950 through 2100. This query will give you the result you want:

  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
    join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;

Result:

|   themonth | COLUMN_1 |
-------------------------
| 2012-11-01 |        9 |
| 2012-12-01 |        1 |

Note that instead of just showing "11" or "12" as month, which doesn't work well if you have ranges going above 12 months, or doesn't help sorting when it crosses a new year, this query shows the first day of the month instead.

If not, you can virtually create a dates table on the fly, per the expanded query below:

;with dates(thedate) as (
  select dateadd(yy,years.number,0)+days.number
    from master..spt_values years
    join master..spt_values days
      on days.type='p' and days.number < datepart(dy,dateadd(yy,years.number+1,0)-1)
   where years.type='p' and years.number between 100 and 150
      -- note: 100-150 creates dates in the year range 2000-2050
      --       adjust as required
)
  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
    join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;

The full working sample is given here: SQL Fiddle

How to get number of days of all month

You can use package https://pub.dev/packages/date_util

With dateUtility.daysInMonth

code snippet

var dateUtility = DateUtil();
var day1 = dateUtility.daysInMonth(2, 2019);
print(day1);
var day2 = dateUtility.daysInMonth(2, 2018);
print(day2);

full code

import 'package:flutter/material.dart';
import 'package:date_util/date_util.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        // This is the theme of your application.
        //
        // Try running your application with "flutter run". You'll see the
        // application has a blue toolbar. Then, without quitting the app, try
        // changing the primarySwatch below to Colors.green and then invoke
        // "hot reload" (press "r" in the console where you ran "flutter run",
        // or simply save your changes to "hot reload" in a Flutter IDE).
        // Notice that the counter didn't reset back to zero; the application
        // is not restarted.
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  // This widget is the home page of your application. It is stateful, meaning
  // that it has a State object (defined below) that contains fields that affect
  // how it looks.

  // This class is the configuration for the state. It holds the values (in this
  // case the title) provided by the parent (in this case the App widget) and
  // used by the build method of the State. Fields in a Widget subclass are
  // always marked "final".

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  int _counter = 0;

  void _incrementCounter() {
    var dateUtility = DateUtil();
    var day1 = dateUtility.daysInMonth(2, 2019);
    print(day1);
    var day2 = dateUtility.daysInMonth(2, 2018);
    print(day2);

    setState(() {
      // This call to setState tells the Flutter framework that something has
      // changed in this State, which causes it to rerun the build method below
      // so that the display can reflect the updated values. If we changed
      // _counter without calling setState(), then the build method would not be
      // called again, and so nothing would appear to happen.
      _counter++;
    });
  }

  @override
  Widget build(BuildContext context) {
    // This method is rerun every time setState is called, for instance as done
    // by the _incrementCounter method above.
    //
    // The Flutter framework has been optimized to make rerunning build methods
    // fast, so that you can just rebuild anything that needs updating rather
    // than having to individually change instances of widgets.
    return Scaffold(
      appBar: AppBar(
        // Here we take the value from the MyHomePage object that was created by
        // the App.build method, and use it to set our appbar title.
        title: Text(widget.title),
      ),
      body: Center(
        // Center is a layout widget. It takes a single child and positions it
        // in the middle of the parent.
        child: Column(
          // Column is also a layout widget. It takes a list of children and
          // arranges them vertically. By default, it sizes itself to fit its
          // children horizontally, and tries to be as tall as its parent.
          //
          // Invoke "debug painting" (press "p" in the console, choose the
          // "Toggle Debug Paint" action from the Flutter Inspector in Android
          // Studio, or the "Toggle Debug Paint" command in Visual Studio Code)
          // to see the wireframe for each widget.
          //
          // Column has various properties to control how it sizes itself and
          // how it positions its children. Here we use mainAxisAlignment to
          // center the children vertically; the main axis here is the vertical
          // axis because Columns are vertical (the cross axis would be
          // horizontal).
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            Text(
              'You have pushed the button this many times:',
            ),
            Text(
              '$_counter',
              style: Theme.of(context).textTheme.display1,
            ),
          ],
        ),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _incrementCounter,
        tooltip: 'Increment',
        child: Icon(Icons.add),
      ), // This trailing comma makes auto-formatting nicer for build methods.
    );
  }
}

Output

I/flutter (11462): 28
I/flutter (11462): 28

SQL: Total days in a month

You can get the number of days of a given date like this:

DECLARE @date DATETIME = '2014-01-01'
SELECT DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date))

And the query:

SELECT  ID
        ,[Date]
        ,[Time]
        ,Value1
        ,Value2
        ,DATEDIFF(DAY, [Date], DATEADD(MONTH, 1, [Date])) AS TotalDayinMonth
        ,Value1 * 100 * DATEDIFF(DAY, [Date], DATEADD(MONTH, 1, [Date])) * Value2 AS Result
FROM yourTable

---

Related Topics