dcsimg
Results 1 to 3 of 3

Thread: last day of the first month of the current quarter

  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Question last day of the first month of the current quarter

    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

  2. #2
    Join Date
    Dec 2004
    Posts
    502
    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Ah, the Details ...

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •