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.
[Solved] Getting the date of the next month start in SQL
[Solved] Getting the date of the next month start in SQL
Last edited by DTC57 on Wed Oct 18, 2017 9:57 am, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
Re: Getting the date of the next month start in SQL
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".
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"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Getting the date of the next month start in SQL
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:
Alternatively, if using this in a Where clause, you code use something like this:
Explanation:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
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"
Code: Select all
Where TO_CHAR("test_date",'YYYY-MM') = TO_CHAR(CURRENT_DATE,'YYYY-MM')
- 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.
- You may change the text of the field in the Select statement to meet your needs.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Getting the date of the next month start in SQL
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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice