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.
Author Messages
bernfc10
Programmer/analyst
State
Veteran Member
(997 points)
Veteran Member
Posts:469


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.
    bernfc10
    Programmer/analyst
    State
    Veteran Member
    (997 points)
    Veteran Member
    Posts:469


    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
    Private
    Private
    Veteran Member
    (471 points)
    Veteran Member
    Posts:163


    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.

    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    06/15/2016 8:47 AM
    The way I do it is to two steps, both using the 900-INCREMENT-DATE API.
    First step is to take desired date/month and calculate the first day of the following month.
    Second step is to subtract one from the result.
    typing this off the cuff, but it's something like this:

    
    MOVE WHATEVER-DATE TO WSDR-FR-DATE.
    MOVE 01 TO WSDR-FR-DAY.
    MOVE 1 TO WSDR-MONTH-INCR.
    MOVE ZEROES TO WSDR-DAY-INCR.
    MOVE ZEROES TO WSDR-YEAR-INCR.
    MOVE "Y" TO WSDR-EOM-ROLLOVER.
    PERFORM 900-INCREMENT-DATE.
    MOVE WSDR-TO-DATE TO WSDR-FR-DATE.
    MOVE ZEROES TO WSDR-MONTH-INCR.
    MOVE -1 TO WSDR-DAY-INCR.
    MOVE ZEROES TO WSDR-YEAR-INCR.
    MOVE "Y" TO WSDR-EOM-ROLLOVER.
    PERFORM 900-INCREMENT-DATE.
    MOVE WSDR-TO-DATE TO WHATEVER-DATE.
    

    Thanks for using the LawsonGuru.com forums!
    John
    kim
    Private
    Private
    Basic Member
    (37 points)
    Basic Member
    Posts:15


    Send Message:

    --
    06/16/2016 12:20 PM
    Here is a Lawson API.
    900-GET-DATE-EOM takes an input date and returns a valid output date set to the end of the month.

    reference: Lawson 4GL Application Program Interfaces
    You are not authorized to post a reply.