[Solved] End of month date

[pinco pallo]

Hello to all, in my database, for a certain date, I would like to calculate the date of the last day of the month.
Example: if today is 02/12/2016, how can I find "31/12/2016"?
In Base with embedded HSQLDB I do not find a suitable function. Can you help me? Thanks in advance.

[Villeroy]

The zeroth of next month for example: =DATE(YEAR(TODAY());MONTH(TODAY())+1;0)

[pinco pallo]

Thanks for the reply but the formula seems more suitable for Calc. My request is for Base with HSQLDB 1.8.

[Villeroy]

I think you can use the spreadsheet formula (or something very similar) with the Oracle Report Builder.

[Sliderule]

I strongly encourage you to NOT use the Embedded Database ( as of the date I am writing this . . . HSQL 1.8.0.10 ) but rather to upgrade to a SPLIT database . . . using HSQL Version 2.3.4 .

This will result in many additional built-in functions, including, date arithmetic, and, safety from NOT having OpenOffice / LibreOffice include your database in the compressed .odb file, plus, you can use other tools to read / write to your database ( external to OpenOffice / LibreOffice Base ).

HSQL Version 2.3.4 includes a function LAST_DAY and if we assume the name name of the field in your database table is "MY_DATE_FIELD" you would use the following code:

Code: Select all

CAST( LAST_DAY("MY_DATE_FIELD") AS DATE) AS "LAST_DAY_OF_MONTH"

Explanation: Since the HSQL 2.3.4 function returns a TIMESTAMP when using the function LAST_DAY . . . by including the CAST function it will result in a DATE column, and, this is what you are looking for.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[charlie.it]

Ciao, the answers of @Villeroy and @Sliderule are very good and I recommend you follow them, but if you want to try this code by staying with embedded HSQLDB, it should work:

Code: Select all

SELECT "ID", "Date",
 case 
   when
      month("Date")=4 or month("Date")=6  or month("Date")=9  or month("Date")=11
   then
      '30/' || right('0'||month("Date"),2) || '/' || right(year("Date"),2)
   when
      month("Date")=2 and  mod(year("Date"), 4) <> 0
   then
      '28/' || right('0'||month("Date"),2) || '/' || right(year("Date"),2)
    when
      month("Date")=2 and  mod(year("Date"), 4) = 0
   then
      '29/' || right('0'||month("Date"),2) || '/' || right(year("Date"),2)

   else '31/' || right('0'||month("Date"),2) || '/' || right(year("Date"),2)
end

as "End of Month"

FROM "tMyTable"

Edit: Thanks to @Sliderule for his correction , end of fourth row:

Code: Select all

 or month("Date")=11

[Villeroy]

This one returns true date values. Just add some date formatting when the query grid shows integers.

Code: Select all

SELECT "ID", "Date", 
	cast(
		year("Date")||'-'|| month("Date") ||
		case when month("Date")=4 or month("Date")=6  or month("Date")=9  or month("Date")=11
		    then '-30'
		when month("Date")=2 and  mod(year("Date"), 4) <> 0
		    then '-28' 
		when month("Date")=2 and  mod(year("Date"), 4) = 0
       		    then '-29' 
                else '-31'
                end
		AS DATE
	)
    as "End of Month"
FROM "tMyTable"

NEW MACRO to convert embedded HSQL1.8: viewtopic.php?f=21&t=86071
Extract the code to your profile.
Add a driver directory with a recent version of hsqldb.jar to the directory where your odb lives.
Run the macro.

[charlie.it]

Ok for CAST function, Villeroy. Your code works well with OO but gives error with LO. Why?

[Villeroy]

I did this with LO 5.1 and tested with an embedded HSQLDB and with HSQL2 in direct and parsed SQL mode.
And with AOO 4.2 alpha it works as well.
Limitations: 1900-02-29 and 2100-02-29 are not valid dates. These two would rise the invalid date error.

[charlie.it]

The error happens with LO 4.3.7, the latest version can be used with Mac OSX 10.7.5.

[Villeroy]

Sorry. However, you can use Sliderules solution with HSQL2.

And you may try this:

Code: Select all

SELECT "D", "ID", 
	CAST( 
		YEAR( "D" ) || '-' || RIGHT('0'|| MONTH( "D" ), 2) || 
			CASE WHEN MONTH( "D" ) IN (4, 6, 9, 11) 
				THEN '-30' 
			WHEN MONTH( "D" ) IN (1, 3, 5, 7, 8, 10, 12) 
				THEN '-31' 
			WHEN MOD(YEAR("D"), 400)=0 OR (MOD( YEAR( "D" ), 100 ) <> 0 AND MOD(Year("D"),4) = 0)
				THEN '-29'
			ELSE '-28' 
			END 
		AS DATE 
		) "End of Month" 
FROM "Data"

where I used RIGHT('0'|| MONTH( "D" ), 2) in order to get a 2-digit month number. May be that's the problem.
Field names and table name from my FilterData.odb

Edit: Fixed the century leap year problem too. Let's strive for an EasterSunday function for HSQL1.8

[charlie.it]

Well Villeroy, all OK now, also with LO 4.3.7 and HSQL1.8.
Thank you.

[pinco pallo]

Hi, I try all the proposed solutions. I like the @charlie.it solution with corrections of @Villeroy but also that of @Sliderule with split database.
Thank you all.