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

Creating tables and queries

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

Postby DTC57 » Tue Oct 17, 2017 4:27 pm

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.
Last edited by DTC57 on Wed Oct 18, 2017 9:57 am, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
DTC57
 
Posts: 4
Joined: Tue Oct 17, 2017 3:50 pm

Re: Getting the date of the next month start in SQL

Postby chrisb » Tue Oct 17, 2017 7:47 pm

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   Expand viewCollapse view
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.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Getting the date of the next month start in SQL

Postby Sliderule » Tue Oct 17, 2017 8:07 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1154
Joined: Thu Nov 29, 2007 9:46 am

Re: Getting the date of the next month start in SQL

Postby Villeroy » Tue Oct 17, 2017 8:13 pm

Code: Select all   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25961
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests