SQL Query to find the last day of the month

Question

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.

Answer 1

I know this question was for SQL Server 2005, but I thought I'd mention- as of SQL 2012, there now is an EOMONTH() function that gets the last day of the month. To get it in the format specified by the original asker you'd have to cast to a datetime.

SELECT CAST(eomonth(GETDATE()) AS datetime)

Answer 2

Select DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

This works great in T-sql ..

Replace the GETDATE() of the query with your column name .

Answer 3

Declare @GivenDate datetime 
Declare @ResultDate datetime 
DEclare @EOMDate datetime 
Declare @Day int 
set @GivenDate=getdate() 
set @GivenDate= (dateadd(mm,1,@GivenDate)) 
set @Day =day(@GivenDate) 
set @ResultDate=dateadd(dd,-@Day+1,@GivenDate) 
select @EOMDate =dateadd(dd,-1 ,@ResultDate) 
select @EOMDate 

Answer 4

http://php.net/manual/en/function.cal-days-in-month.php

The length in days of the selected month in the given calendar

<?php
$num = cal_days_in_month(CAL_GREGORIAN, 8, 2003); // 31
echo "There was $num days in August 2003";
?>