calculate the last day of any month

I have the logic encapsulated in a SQL UDF if you want it. The actual calc is just one line.

Hi MikeP, sure. Thank you!

 

 

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

 

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.

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:

[code] 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. [/code]

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