Find the date of the end of the month in various languages (C #, Java, Ruby, Javascript, VBA, Excel, MySQL, SQLite3, Oracle)

Introduction

When developing systems and tools for business use, the last day of the month may sometimes be required, for example, "the billing date in the invoice" or "the execution date of the closing process". Therefore, I investigated a typical method for finding the date of the end of the month in various languages.

C#

• Use the DaysInMonth method of the DateTime structure to find the "days of the month", then use the number of days found to create a DateTime object to find the last day of the month.
• [Digression] If you look at the Official API, it says "DateTime Struct". , It seems that the correct expression is to call it a DateTime structure instead of the DateTime class.

LastDayOfMonth.cs

public class LastDayOfMonth
{
    public static void CalcLastDayOfMonth()
    {
        DateTime today = DateTime.Now;
        int days = DateTime.DaysInMonth(today.Year, today.Month);
        System.Console.WriteLine(new DateTime(today.Year, today.Month, days).ToString("yyyy-MM-dd"));
    }
}

Java

• For Java8 and above, you can use the YearMonth class to find the end of the month directly.
• Use YearMonth.now () if you want to use the current date as a reference, and use YearMonth.of (int year, int month) if you want to specify an arbitrary year and month.
• There is also a way to use the LocalDate class, but using the YearMonth class is shorter and cleaner.
• Thank you for pointing out, @swordone.
• For Java7, you need to write the process with Calendar class.
• Reference URL: Handling date and time in Java (up to Java7)

LastDayOfMonth.java

import java.time.LocalDate;
import java.time.YearMonth;

public class LastDayOfMonth {
	public static void main(String[] args) {
		LocalDate lastDay = YearMonth.now().atEndOfMonth();
		System.out.println(lastDay.toString());
	}
}

Ruby

• Specify a negative value (-1) for the third argument of the Date class constructor to find the last day of the month.

irb

irb(main):001:0> require 'date'
=> true
irb(main):002:0> today = Date.today
=> #<Date: 4917313/2,0,2299161>
irb(main):003:0> puts Date.new(today.year, today.month, -1)
2019-06-30
=> nil

Javascript

• Specify "0th day of the month following any month" in the constructor of the Date class to find the last day of the month.

var today = new Date();
var last_day = new Date(today.getFullYear(), today.getMonth() + 1, 0);
document.write(last_day);

VBA

• Use the DateSerial function to specify "0th day of the month following any month" to find the last day of the month.

Module1

Public Sub PrintLastDayOfMonth()
  Debug.print DateSerial(Year(Now()), Month(Now())+1, 0)
End Sub

Excel (worksheet function)

• When using the DATE function, specify "0th day of the month following any month" to find the last day of the month.
• You can find the last day of this month by embedding the following formula in the cell.
• When using the EOMONTH function, specify "0th day of any month" to find the last day of the month.
• The months specified by the DATE function (next month) and the EOMONTH function (current month) are different.
• "EOMONTH" seems to be an abbreviation for "End Of Month".

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)

=EOMONTH(NOW(),0)

MySQL

• Specify "arbitrary date" in the LAST_DAY function to find the last day of the month.
• In the following example, the current date and time is specified by the NOW function, but you can also pass a date string such as '2019-06-22' as an argument.

mysql> SELECT LAST_DAY(NOW());
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2019-06-30      |
+-----------------+
1 row in set (0.00 sec)

SQLite3

• Specify "one day before the first day of the month following any date" in the date function to find the last day of the month.
• After specifying the current date with'now' as an arbitrary date ... *'+1 month' is the next month,'start of month'is the first day of the month, and'-1 days' is one day before.

sqlite> select date('now', '+1 months', 'start of month', '-1 days');
2019-06-30

Oracle

• Specify "arbitrary date" in the LAST_DAY function to find the last day of the month.
• In the example below, the current date and time is specified by SYSDATE, but you can also pass a date such as TO_DATE ('2020/02/19','YYYY / MM / DD') as an argument.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY(SYSDATE)
-----------------
29-FEB-20

Summary

• I felt that there might be a process to get the last day of the month by inserting a large date that does not exist, such as "June 35, 2019", but I could not find it as far as I searched.
• MySQL and Oracle "LAST_DAY" is intuitive.
• I also found it very easy to understand that the name of the function is "the last day of the month".
• Excel's EOMONTH function is also very simple.
• I expected that "Excel has few functions and it would be difficult to find the last day of the month ...", but I was betrayed brilliantly.
• SQLite3 is ~~ metamorphic ~~ strange.
• The date function requires as many as four arguments, and you must pass arguments that look like natural statements.

Reference URL

• C#
• How to find the date of the beginning / end of the month? [C #, VB]
• Add a method to get the first day and last day of the month to DateTime type of C #
• Java
• Get the end of the month with the Date-Time API.
• ~~ Get last day with [Java] LocalDate ~~
• Ruby
• [Ruby] Get the date at the end of the month
• Javascript
• Get the last day with JavaScript
• VBA
• Get the last day of this month / previous month / next month
• Excel (worksheet function)
• What day is the end of this month? Find the last day of the month with the DATE function
• How to find the end date / EOMONTH function
• MySQL
• Get the date of the end of the month
• SQLite3
• Get date and time (date function, time function, datetime function, julianday function, strftime function)
• Oracle
• Calculate the date of the end of the month, the same month of the month, the last day of the same time