[Solved] Getting the date of the next month start in SQL

[DTC57]

I need to check whether a given date field is within the current month. I can create the date of the start of the month [ TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) ], but cannot see how to create either the last date in the current month or the first of the following month. Can anyone give me a hint?

Thanks, David

Thanks to all for the ideas. In particular, the idea of converting all the dates to a month-based string and checking against that occurred to me too. What was hard to work out was how to construct a date value rather than a string value. In other languages there is a function that does this, taking the year, month, day etc. as numbers and creating a date value as a result.

[chrisb]

DTC57,
you could simply use the month() function, possibly in conjunction with the year() function.
i have used the field name "Date" and the table name of "MyTable".

Code: Select all

select "Date",
case when month("Date") = month(current_date) then 'Y' end "CurrentMonth",
case when month("Date") = month(current_date) and year("Date") = year(current_date) then 'Y' end "CurrentMonthAndYear"
from "MyTable"

[Sliderule]

David:

Welcome to the OpenOffice / LibreOffice Base forum.

You did not say, but, I will assume the database back-end you are using is an Embedded Database, as indicated on the status line ( at the bottom ) after you open ( start ) your Base file ( *.odb ).

The TO_CHAR HSQL function returns a text string, NOT a field type of Date. Therefore, you can ask to have it return a text string of merely, YEAR and MONTH . . . for example, 'YYYY-MM' .

So, you can use that, if as a return value in the Select part of a Query, for example:

Code: Select all

CASE WHEN TO_CHAR("test_date",'YYYY-MM') = TO_CHAR(CURRENT_DATE,'YYYY-MM') THEN 'DATE IS IN CURRENT MONTH' Else 'DATE IS NOT IN CURRENT MONTH' END as "DATE_IN_CURRENT_MONTH"

Alternatively, if using this in a Where clause, you code use something like this:

Code: Select all

Where TO_CHAR("test_date",'YYYY-MM') = TO_CHAR(CURRENT_DATE,'YYYY-MM')

Explanation:

1. The above assumes your database contains a column type of DATE with a name of "test_date" , change this in your Query to how you have defined your table.
2. You may change the text of the field in the Select statement to meet your needs.

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.

[Villeroy]

Code: Select all

SELECT "DATE", CAST(YEAR("DATE")+CASEWHEN(MONTH("DATE")=12,1,0) ||'-'|| MOD(MONTH("DATE"), 12) +1 ||'-01' AS DATE) AS "Next First"
FROM "TBL"