Menu
I need to get the last day of the month given as a date in SQL. If I have the first day of the month, I can do something like this:
DATEADD(DAY, DATEADD(MONTH,'2009-05-01',1), -1) But does anyone know how to generalize it so I can find the last day of the month for any given date?
732
MySQL LAST_DAY() Function The LAST_DAY() function extracts the last day of the month for a given date.
The logic is very simple. The first part @DATE-DAY(@DATE) results to the Last day of a previous month and adding 1 to it will result on the first day of current month. The second part EOMONTH(@DATE) makes use of SYSTEM function EOMONTH which results to the last day of the given date.
Here's how this works: First we format the date in YYYYMMDD... format truncating to keep just the 6 leftmost characters in order to keep just the YYYYMM portion, and then append '01' as the month - and voila! you have the first day of the current month.
An optional integer expression that specifies the number of months to add to start_date. If the month_to_add argument has a value, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.
From SQL Server 2012 you can use the EOMONTH function.
Returns the last day of the month that contains the specified date, with an optional offset.
Syntax
EOMONTH ( start_date [, month_to_add ] ) How ... I can find the last day of the month for any given date?
SELECT EOMONTH(@SomeGivenDate) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
