calculate the last day of any month

Sort:
You are not authorized to post a reply.
Author
Messages
Chesca
Veteran Member
Posts: 490
Veteran Member

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

    mikeP
    Veteran Member
    Posts: 151
    Veteran Member
      I have the logic encapsulated in a SQL UDF if you want it. The actual calc is just one line.
      Chesca
      Veteran Member
      Posts: 490
      Veteran Member
        Hi MikeP, sure. Thank you!
        mikeP
        Veteran Member
        Posts: 151
        Veteran Member

           

           

          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
          Veteran Member
          Posts: 164
          Veteran Member
            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
            Senior Member
            Posts: 3348
            Senior Member
              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
              Basic Member
              Posts: 15
              Basic Member
                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.