PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 06/16/2016 12:20 PM by  kim
calculate the last day of any month
 6 Replies
Sort:
You are not authorized to post a reply.
Page 1 of 212 > >>
Author Messages
Chesca
Programmer/analyst
State
Veteran Member
(1041 points)
Veteran Member
Posts:489


Send Message:

--
06/14/2016 12:57 PM

    Is there an API I could use to calculate the last day of any month?  

    mikeP
    Private
    Private
    Veteran Member
    (407 points)
    Veteran Member
    Posts:151


    Send Message:

    --
    06/14/2016 1:12 PM
    I have the logic encapsulated in a SQL UDF if you want it. The actual calc is just one line.
    Chesca
    Programmer/analyst
    State
    Veteran Member
    (1041 points)
    Veteran Member
    Posts:489


    Send Message:

    --
    06/14/2016 1:31 PM
    Hi MikeP, sure. Thank you!
    mikeP
    Private
    Private
    Veteran Member
    (407 points)
    Veteran Member
    Posts:151


    Send Message:

    --
    06/14/2016 1:49 PM

     

     

    Here's the code to load the UDF into SQL Server.  The calc logic is the line that starts with "SET @LastDayOfMonth.."  It returns the last day of the month for the date in @SelectedDate.  I got the logic somewhere on the web.  Probably no reason you can't translate it to whatever you're coding in.

     

    USE [db name here]
    GO


    -- Drop the function if it's already in place to avoid error trying to add it again.
    IF ( OBJECT_ID('dbo.LAST_DAY') IS NOT NULL )
        DROP FUNCTION [dbo].[LAST_DAY]
    GO

    /****** Object:  UserDefinedFunction [dbo].[LAST_DAY]    Script Date: 11/27/2012 8:18:36 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        Mike Palandri
    -- Create date: 11/21/2012
    -- Description:    Returns last day of month in which passed date falls
    -- =============================================
    CREATE FUNCTION [dbo].[LAST_DAY]
    (
        -- Add the parameters for the function here
        @SelectedDate datetime
    )
    RETURNS date
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @LastDayOfMonth datetime

        -- Add the T-SQL statements to compute the return value here
        SET @LastDayOfMonth = (SELECT CONVERT(char(10), DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0,@SelectedDate)+1,0)),101))

        -- Return the result of the function
        RETURN @LastDayOfMonth

    END
    GO

    GRANT Execute ON [dbo].LAST_DAY TO PUBLIC
    GO

     

    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    06/14/2016 2:24 PM
    When I have had to do this, I have used the "900-IS-DATE-VALID" API. I began with 31, ran it through the API, if it was invalid, I would then go to 30 and so on. Very simple to do.

    You are not authorized to post a reply.
    Page 1 of 212 > >>