Menu
I need to find the last day of a month in the following format:
"2013-05-31 00:00:00:000" Anybody please help out.
276
If you would like to get the date containing the last day of the month of a given date, use the EOMONTH() function. This function takes one mandatory argument: a date (or date and time), which can be a date/datetime column or an expression that returns a date. (In our example, we use the PurchaseDate column.)
MySQL LAST_DAY() Function The LAST_DAY() function extracts the last day of the month for a given date.
For the last day of the previous month: SELECT EOMONTH(DATEADD(MONTH,-1,GETDATE())); For the last day of the previous month formatted as needed: SELECT CONVERT(VARCHAR(10), EOMONTH(DATEADD(MONTH,-1,GETDATE())), 101);
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.
Try this one -
CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] ( @Date DATETIME ) RETURNS DATETIME AS BEGIN RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0)) END Query:
DECLARE @date DATETIME SELECT @date = '2013-05-31 15:04:10.027' SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)) Output:
----------------------- 2013-05-31 00:00:00.000 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
