last day of the first month of the current quarter

[Sabyasachi]

Need a query to have the last day of the first month of the current query.
I got a function which returns me only the lastday for the 1quater and third quater,but when i check for 2nd one itw give me error
CREATE FUNCTION GetlastDayOfQuarter
( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN

DECLARE @OutputDate DATETIME

SET @OutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(DATEPART(Q, @pInputDate) * 3 - 2 AS VARCHAR(2)) +'/31'

RETURN @OutputDate

END
GO

--select dbo.GetlastDayOfQuarter ('04/01/2008')


please reply

[nosepicker]

If you are using SQL Server, here is a single statement that should return the last day of the first month of whatever quarter your input date is in. In this case, I'm using the getdate:

SELECT DATEADD(month, DATEDIFF(month, -1, DATEADD(quarter, DATEDIFF(quarter, -1, getdate()), -1)) -2, -1)

BTW, your function didn't work for the second quarter because the last day of the first month of the second quarter is 4/30/2008, and you are trying to create an invalid date of 4/31/2008.

[Island1]

Those troublesome details ... like using non-existent dates in a function... And the RDBMS is sooooo unforgiving ...

Did this resolve your difficulties? Let us know if you encounter further issues / have more questions!

Bill