[Solved] Date BETWEEN AND

dreamquartz · Post by **dreamquartz** » Wed May 27, 2020 3:00 am

Hi All,

Looking for a method to select a period BETWEEN AND.
I understand

WHERE "tPerson"."DateOfBirth" BETWEEN :DateFromStart AND :DateToEnd

, but this requires me to enter full dates, like BETWEEN '07-11-2010' AND '07-13-2010' to select a period.
I also want to be able to select BETWEEN July 2010 AND September 2010 (where the months are numbers) as period.

I tried something like and variations to

Code: Select all

BETWEEN '%' || :DateFromStart || '%' AND '%' || :DateToEnd || '%'

, but that leads to errors

data exception: invalid datetime format.

Any idea to solve something like this?

Dream

edit this topic's initial post · Post by **Villeroy** » Wed May 27, 2020 10:34 am

With 4 numbers :Y1, :Y2, :M1, :M2

Code: Select all

SELECT * FROM "TBL"
WHERE (YEAR("D")= :Y1 AND MONTH("D")>= :M1) 
OR (YEAR("D")= :Y2 AND MONTH("D")<= :M2)
OR (YEAR("D")> :Y1 AND YEAR("D") < :Y2)

Either the month is >= :M1 within :Y1
or the month is <= :M2 within :Y2
or the year is between (but not including) :Y1 and :Y2

Edit: Find the error and fix it!

edit this topic's initial post · Post by **Villeroy** » Wed May 27, 2020 12:35 pm

Another approach concatenating ISO dates from numbers and converting them to dates:

Code: Select all

SELECT * FROM "TBL"
WHERE "D" >= CAST(:Y1 ||'-'|| :M1 ||'-1' AS DATE)
AND "D" < CAST(:Y2 + CASEWHEN(:M2 = 12,1,0) ||'-'|| MOD(:M2, 12) +1 ||'-1' AS DATE)

The idea is to match records >= 1st of :M1 in :Y1
and before 1st of :M1 +1 in :Y2
Since it is difficult to find out the last day of the second month (28,29,30 or 31) we select anything before (but not including) the 1st of next month after :M2.
If the given month = 12, the next month is 1 and the year is the next year.

edit this topic's initial post · Post by **Villeroy** » Thu May 28, 2020 11:05 am

And this is what you get when you apply the usual set of tools and tricks.

dreamquartz · Post by **dreamquartz** » Thu May 28, 2020 7:37 pm

Villeroy wrote:And this is what you get when you apply the usual set of tools and tricks.

I like your thoughts.

However (LOL), I have tables filled with dates, like "tPerson" with "DateOfBirth".
These dates are completely random, and I would like to have the User select arbitrarily BETWEEN 'MM/YYYY' AND 'MM/YYYY'.

I am looking at the DateTime functions, CAST, CONVERT, TO_CHAR and so on and so forth, but there appears nothing available to where a DATE can be filtered in the MM/YYYY format, to be used as the start or the end of the 'BETWEEN' - 'AND' function.

Any thoughts,

Dream

edit this topic's initial post · Post by **Villeroy** » Thu May 28, 2020 9:10 pm

qParamMonths as used in the example database:

Code: Select all

SELECT * FROM "TBL" 
WHERE ( "D" >= CAST( :Y1 || '-' || COALESCE ( :M1, 1 ) || '-1' AS DATE ) 
    OR :Y1 IS NULL ) 
AND ( "D" < CAST( :Y2 + CASEWHEN( COALESCE ( :M2, 12 ) = 12, 1, 0 ) || '-' || MOD( COALESCE ( :M2, 12 ), 12 ) + 1 || '-1' AS DATE ) 
    OR :Y2 IS NULL )

We select all columns from the single table "TBL". It is just one column in that table. The returned record set would be editable if I defined a primary key.
The WHERE condition consists of 2 conditions connected with AND. Both main conditions consist of 2 sub-conditions connected with OR.

Code: Select all

WHERE (<comparison_from> OR :Y1 IS NULL) AND (<comparison_until> OR :Y2 IS NULL)

If :Y1 is null, the first AND condition is TRUE anyway because of the OR operator: WHERE TRUE AND <comparison_until>
If :Y2 is null, the second AND condition is TRUE anyway: WHERE <comparison_from> AND TRUE
If both :Y1 and :Y2 are null, the expression expands to WHERE TRUE AND TRUE which simply returns all the records regardless of any date comparison.
If both :Y1 and :Y2 are given, the expression expands to WHERE <comparison_from> AND <comparison_until>. A record is selected if both comparisons return TRUE.

Code: Select all

"D" >= CAST( :Y1 || '-' || COALESCE ( :M1, 1 ) || '-1' AS DATE )

is the <comparison_from>. It compares the date field "D" with a start date which is a concatenated ISO date representing the 1st day of the given year and month: :Y1-Month-1.
COALESCE(:M1,1) substitutes a missing :M1 with the smallest month number 1 in order to compare the first month of the given year.
CAST('2020-5-1' AS DATE) converts the concatenated ISO string into a true date value comparable with the date values in the "D" column.
"D" >= CAST('2020-5-1' AS DATE) returns TRUE for every "D" being equal or bigger than 1st of May 2020.

Code: Select all

"D" < CAST( :Y2 + CASEWHEN( COALESCE ( :M2, 12 ) = 12, 1, 0 ) || '-' || MOD( COALESCE ( :M2, 12 ), 12 ) + 1 || '-1' AS DATE )

is the <comparison_until>. It compares the date field "D" with an end date which is a concatenated ISO date representing the 1st day of the month following the given month: :Y2-[Month+1]-1.
COALESCE(:M2,12) substitutes a missing :M2 with the highest month number 12. If no month is given we assume the last month of the given year as given month.
:Y2 + CASEWHEN( month = 12, 1, 0 ) adds 1 to :Y2 if the given month equals 12 and 0 otherwise.
MOD returns the residue of a division. MOD( month , 12 ) returns the same month number for months 1 to 11 and 0 for 12, so we get the next month number when we add +1. Next month after the 12 (December) is 1 (January). Instead of MOD( COALESCE ( :M2, 12 ), 12 ) we could use the longer expression CASEWHEN(COALESCE(:M2,12) = 12, 1, COALESCE(:M2,12)) alternatively.
CAST('2020-5-1' AS DATE) converts the ISO string into a true date value which can be compared with the date values in the "D" column.
If :Y2 = 2020 and :M2 is missing, COALESCE gives month number 12, MOD(12,12)+1 gives 1 and CASEWHEN(12 = 12, 1, 0) adds one to the year number.
The comparison reads "D" < CAST('2021-1-1' AS DATE) which returns TRUE for every "D" before the calculated date 2021-1-1. This will work with any given month even though we ignore how many days the given month has.
--------------------------------------------------------------------------
If you compare the query "qReportMonths" with the above parameter query, you will see that I only added the relation to the filter record and substituted the 4 parameters with the filter record's 4 criteria values. I forgot to substitute a COALESCE in the <condition_until>. Because of that mishap, the query returns no records when the M2 is missing while Y2 is given. This query returns a read-only row set because of the second table which is perfectly OK for reporting.

edit this topic's initial post · Post by **Villeroy** » Thu May 28, 2020 9:14 pm

dreamquartz wrote: I am looking at the DateTime functions, CAST, CONVERT, TO_CHAR and so on and so forth, but there appears nothing available to where a DATE can be filtered in the MM/YYYY format, to be used as the start or the end of the 'BETWEEN' - 'AND' function.

The order of rows and columns in a database does not matter. This will work with any amount of columns and rows in any order of columns and rows. I simply select * from some table based on some slightly advanced conditions that refer to given parameters and a date column. The BETWEEN operator does not apply in this case because the <comparison_until> must not include the parameter value. BETWEEN A AND B always includes A and B

edit this topic's initial post · Post by **Villeroy** » Thu May 28, 2020 9:51 pm

I used the query editor as a development platform.

Code: Select all

SELECT "Filter".*, 
Y1 ||'-'|| M1 || '-1' AS "From", 
Y2 ||'-'|| M2 || '-31' AS "Until" 
FROM "Filter"

When you fill out all 4 fields M1, M2, Y1, Y2 you get this after saving the record and hitting F5

Code: Select all

M1	M2	Y1	Y2	ID	From	Until
1	2	2016	2016	0	2016-1-1	2016-2-31

which is a good start with concatenated ISO strings.
What happens when we convert them to dates?

Code: Select all

SELECT "Filter".*, 
CAST(Y1 ||'-'|| M1 || '-1' AS DATE) AS "From", 
CAST(Y2 ||'-'|| M2 || '-31' AS DATE) AS "Until" 
FROM "Filter"

Code: Select all

M1	M2	Y1	Y2	ID	From	Until
1	2	2016	2016	0	2016-01-01	2016-03-02

2nd of March is the wrong until date because month 2 of leap year has 29 days. So I have to refine things.

Code: Select all

SELECT "Filter".*, 
CAST(Y1 ||'-'|| M1 || '-1' AS DATE) AS "From", 
CAST(Y2 ||'-'|| M2 +1 || '-1' AS DATE) AS "Until" 
FROM "Filter"

gives the 1st of next months first day as until date but this fails when M2=12 because the database throws a data type error when it should convert a date with month 13.
We have to refine further:

Code: Select all

SELECT "Filter".*, 
CAST(Y1 ||'-'|| M1 || '-1' AS DATE) AS "From", 
CAST(Y2 +CASWHEN(M2=12, 1, 0) ||'-'|| MOD(M2,12) +1 || '-1' AS DATE) AS "Until" 
FROM "Filter"

Code: Select all

M1	M2	Y1	Y2	ID	From	Until
1	12	2016	2016	0	2016-01-01	2017-01-01

this adds one to the year number and gives month number 1 when M2 is 12.
It takes much more than a few steps to refine this query until you become sure that you always get the right date values for any combination of years and months. I spent an hour until getting to this state where I concatenate and convert the right date values to be used in the WHERE clause of my parameter query. This is development work. You can not presume that the BETWEEN operator provides a solution. BETWEEN is convenient but it can not be used with this approach.

Edit: You can remove most of the dates in my test table in order to get smaller result sets. Just delete all dates in the middle of a month:
DELETE FROM "TBL" WHERE DAY("D") BETWEEN 4 and 26
This saves a lot of scrolling and you get the first and the last couple of days for any month

edit this topic's initial post · Post by **Villeroy** » Thu May 28, 2020 10:12 pm

Instead of telling us what you are after, you insist that some operator should provide a solution. You do not even tell us the database you are using.

However (LOL), I have tables filled with dates, like "tPerson" with "DateOfBirth".

To find the persons celebrating birthday within a month regardless of the year:

Code: Select all

SELECT * FROM "TBL" WHERE MONTH("D")= :M

If you need the age from a given year number :Y

Code: Select all

SELECT "TBL".*, :Y - YEAR("D") AS "Age"
FROM "TBL" 
WHERE MONTH("D")= :M

returns all records of a given month :M plus the age at given year :Y

and if you need the birthdays within a span of months:

Code: Select all

SELECT "TBL".*, :Y - YEAR("D") AS "Age"
FROM "TBL" 
WHERE MONTH("D") BETWEEN :M1 AND :M2

but now you start testing and find out that this returns nothing when you query from December until March because 12 is bigger than 3 and you start testing with:

Code: Select all

SELECT "TBL".*, :Y - YEAR("D") AS "Age"
FROM "TBL" 
WHERE MONTH("D") BETWEEN :M1 AND CASEWHEN(:M1<:M2, :M2, 12)
OR MONTH("D") BETWEEN CASEWHEN(:M1<:M2, 12, 1) AND :M2

With 2 and 4 this gives
MONTH("D") BETWEEN 2 AND 4
OR MONTH("D") BETWEEN 12 AND 1
(between lower and higher OR between 12 and 1)
with 10 and 2 we get:
MONTH("D") BETWEEN 10 AND 12
OR MONTH("D") BETWEEN 1 AND 2
(between the higher M1 and 12 OR between 1 and the lower M2)

but now the age calculation needs further adjustment ...

chrisb · Post by **chrisb** » Fri May 29, 2020 12:58 am

Edit: i made this post in haste late last night without thoroughly reading through the topic.
today after looking at the posts of Villeroy i regret posting because my code & his code return the same data & i apologise to him.
the most reliable way to filter by year/month is to use concatenation i.e. year("Date") || month("Date").
if the replies of Villeroy do not provide a solution then i am misunderstanding the issue as described in the first post of dreamquartz.

@dreamquartz,

NOTE: hsqldb 2.x only. will not work with embedded.

using hsqldb 2.5.0 i made a parameter query (i think that's what you want) which seems to work ok.
you are prompted for input once.
input takes the form 'YYYYMMYYYYMM' so for example to retrieve dates between january 2018 & december 2019 input would be '201801201912' 12 digits no spaces.
all months prior to october (month 10) must be preceded by '0' i.e. 01, 02, 03 for jan, feb, march.

replace the single instance of "tMyTableName" with "YourTableName".
replace 5 instances of "Date" with "YourDateFieldName".

Code: Select all

select "Date"
from
	(select "Date", year("Date") || right('0' || month("Date"), 2) "YearMonth" from "tMyTableName") m,
	(
		select
			left(s, 6) d1,
			substr(s, 7, 6) d2
		from
		(
			select cast(s as varchar(12)) s
			from
			(values(:YYYYMMYYYYMM)) a (s)
		) 
	) z
where m."YearMonth" between z.d1 and z.d2
order by "Date"

edit this topic's initial post · Post by **Villeroy** » Fri May 29, 2020 1:03 pm

And HSQL2 has a built-in function LAST_DAY(date) returning the last day of a given month which allows us to avoid some of the trouble I had (and use the sacred BETWEEN operator btw.)

This is a simplified version of my parameter query for HSQL2 with mandatory months and mandatory year numbers:

Code: Select all

SELECT * FROM "TBL" 
WHERE "D" 
    BETWEEN CAST( :Y1 || '-' ||  :M1 || '-1' AS DATE ) 
    AND LAST_DAY( CAST( :Y2 || '-' || :M2 || '-1' AS DATE ) )

With optional months:

Code: Select all

SELECT * FROM "TBL" 
WHERE "D" 
    BETWEEN CAST( :Y1 || '-' || COALESCE ( :M1, 1 ) || '-1' AS DATE ) 
    AND LAST_DAY( CAST( :Y2 || '-' || COALESCE ( :M2, 12 ) || '-1' AS DATE ) )

And this should work with optional year numbers:

Code: Select all

SELECT * FROM "TBL" 
WHERE ("D" >= CAST( :Y1 || '-' || COALESCE ( :M1, 1 ) || '-1' AS DATE ) OR :Y1 IS NULL ) 
  AND ("D" <= LAST_DAY( CAST( :Y2 || '-' || COALESCE ( :M2, 12 ) || '-1' AS DATE ) OR :Y2 IS NULL )

or even this one with the sacred operator:

Code: Select all

SELECT * FROM "TBL" 
WHERE "D" 
    BETWEEN CAST( COALESCE ( :Y1, ( SELECT MIN( YEAR( "D" ) ) FROM "TBL" ) ) || '-' || COALESCE ( :M1, 1 ) || '-1' AS DATE ) 
    AND LAST_DAY( CAST( COALESCE ( :Y2, ( SELECT MAX( YEAR( "D" ) ) FROM "TBL" ) ) || '-' || COALESCE ( :M2, 12 ) || '-1' AS DATE ) )

edit this topic's initial post · Post by **Villeroy** » Fri May 29, 2020 3:01 pm

And this is my initial approach without concatenation entirely based on year numbers and month numbers:

Code: Select all

SELECT * FROM "TBL" 
WHERE ( YEAR( "D" ) = :Y1 AND YEAR( "D" ) = :Y2 AND MONTH( "D" ) BETWEEN :M1 AND :M2 ) 
        OR ( YEAR( "D" ) = :Y1 AND YEAR( "D" ) < :Y2 AND MONTH( "D" ) BETWEEN :M1 AND 12 ) 
        OR ( YEAR( "D" ) > :Y1 AND YEAR( "D" ) = :Y2 AND MONTH( "D" ) BETWEEN 1 AND :M2 )
        OR ( YEAR( "D" ) > :Y1 AND YEAR( "D" ) < :Y2 )

If both year numbers are the same then between M1 and M2
If Y2 is greater, then select between M1 and 12 in Y1
If Y2 is greater, then select between 1 and M2 in Y2
or any date where the year is between (but not including) Y1 and Y2
-------------------------------
This should work with any type of database (even with a spreadsheet connection).

Edit: not quite. A spreadsheet connection does not understand x BETWEEN a AND b. You have to replace that with x>=a AND x<=b. Then it filters spreadsheets, text files and dBase files by month and year numbers

dreamquartz · Post by **dreamquartz** » Sat May 30, 2020 7:27 pm

@chrisb
For clarification: I want to be able to select a random period in Months and Years, using 'BETWEEN'-----'AND'. Where a value is recognized by HSQLDB as a DATE, the User must enter a full date, e.g. 04-23-1985. I want the User to have the ability to enter 09-2017 as a selection of a date.
The problem appears to be that HSQLDB does not recognize 09-2017 as a DATE.

Dream

dreamquartz · Post by **dreamquartz** » Sat May 30, 2020 7:47 pm

I have a thought.

The principle of my request is always based on the following: First Date: 01-MM1-YYYY1; Second Date: 01-MM2-YYYY2.
My question is therefore: Is it possible to have the User enter MM1-YYYY1 and have HSQLDB concatenate the entry to 01-MM1-YYYY1 and now recognize it as a Date?

Something like this

Code: Select all

	SELECT
	 "tProjectDateTime"."DateOfProjectStart",
	 TO_CHAR( "tProjectDateTime"."DateOfProjectStart", 'YY' ) "ProjectYR",
	 TO_CHAR( "tProjectDateTime"."DateOfProjectStart", 'YYYY' ) "ProjectYear",
	 TO_CHAR( "tProjectDateTime"."DateOfProjectStart", 'MM-YYYY' ) "ProjectMonthYear",
	 "tServiceUsage"."ServiceUsageID",
	 "tServiceUsage"."FKServiceID",
	 "tProjectDateTime"."ProjectDateTimeID"
 FROM
	 "tProjectDateTime", "tServiceUsage"
 WHERE
	 "tProjectDateTime"."FKServiceUsageID" = "tServiceUsage"."ServiceUsageID" AND
	 CAST ( '01-' || :ProjectMonthYear AS DATE)

It does provide an Error right now

I'm working on it, but suggestions are always appreciated,

Dream

edit this topic's initial post · Post by **Villeroy** » Sat May 30, 2020 7:59 pm

What if the user makes mistakes and enters q2-13-173 ?
Which is why I offer a list box of months and a list box of years.
However, it is trivial to split 09-1972 or even 9-72 into 9 and 1972. The substring and the locate function do this.

dreamquartz · Post by **dreamquartz** » Sat May 30, 2020 8:04 pm

Villeroy wrote:What if the user makes mistakes and enters q2-13-173 ?
Which is why I offer a list box of months and a list box of years.
However, it is trivial to split 09-1972 or even 9-72 into 9 and 1972. The substring and the locate function do this.

Errors are made, for sure.
However, making the User understand what entry options there re, it should not be a problem.
We have standardized all entry formats, and all dates are already stored.
The function is only a lookup function for the User. The user can not modify this data.

The moment the results do not make any sense, the User has to do a re-entry of the info anyway.

Dream.

edit this topic's initial post · Post by **Villeroy** » Sun May 31, 2020 12:41 am

See Form2 which is based on query "qSplit" and utilizes pattern fields for text input 00-0000

Code: Select all

SELECT "TBL".* 
FROM "TBL","Filter" 
WHERE "Filter"."ID"=0 
AND "D">=CAST(CAST( substr( "TXT1", LOCATE( '-', "TXT1" ) + 1 ) AS SMALLINT ) || '-' || CAST( substr( "TXT1", 1, LOCATE( '-', "TXT1" ) - 1 ) AS TINYINT ) || '-1' AS DATE)
AND "D"< CAST(CAST( substr( "TXT2", LOCATE( '-', "TXT2" ) + 1 ) AS SMALLINT ) + CASEWHEN( CAST( substr( "TXT2", 1, LOCATE( '-', "TXT2" ) - 1 ) AS TINYINT ) = 12, 1, 0 ) || '-' || (MOD( CAST( LEFT( "TXT2", LOCATE( '-', "TXT2" ) - 1 ) AS TINYINT ), 12 ) + 1) || '-1' AS DATE)

This works with embedded HSQL and HSQL2. Sliderule's query works with HSQL2.

Post by **Sliderule** » Sun May 31, 2020 2:20 am

dreamquartz wrote:The problem appears to be that HSQLDB does not recognize 09-2017 as a DATE.

To any user other than dreamquartz - It is true, HSQL does not recognise 09-2017 as a DATE since 09-2017 is NOT a DATE. This is exactly as it should be.

To dreamquartz, important news, 09-2017 is NOT a date. Your database lives in the real world, not in the world of make believe text strings that are not real dates.

Code: Select all

Select 
   "MY_TABLE".*,
From "MY_TABLE"
Where TO_CHAR("MY_TABLE"."MY_DATE_COLUMN",'YYYY-MM') Between 
      TO_CHAR(TO_DATE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(:Enter01_From_Date_MM_YYYY),' * ',' '),'[^0-9]','-'),'MM-YY'),'YYYY-MM') 
  and TO_CHAR(TO_DATE(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(:Enter02_To_Date_MM_YYYY),' * ',' '),'[^0-9]','-'),'MM-YY'),'YYYY-MM')

Explanation:

Use the following HSQL 2.x Built-In Functions ( NOT available with HSQL Embedded Database )
1. TO_CHAR
2. TO_DATE
3. REGEXP_REPLACE
4. TRIM
Change "MY_TABLE" to the name of your table
Change "MY_DATE_COLUMN" to the name of your DATE column

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

dreamquartz · Post by **dreamquartz** » Sun May 31, 2020 10:29 pm

@Villeroy,

That works indeed.
However, it is preferred to have it as a selection criterium within a Query, where other parameters can be set as well, like any personal info like, Surnane, GivenName, NickName, Address, PhoneNumber, EmailAddress and so on and so forth.

The Query where THis time-frame principle should be part of, is a massive one indeed, and is to be used by the User to search, and identify (groups of) people.

The time-frame principle is to be used in other Queries as well.

I therefore chose for the solution Sliderule provided.

@Sliderule,

Thank you for responding. This is highly appreciated.

I understand your initial comment. That is where I had an issue with.
Dates are a totally different 'Beast'.

The solution you provided is what I was looking for.
I needed to dig into 'REGEXP_REPLACE', because there was no need to use it ever.
This is quite a useful command.

@Everyone,
For some additional explanation of 'REGEXP_REPLACE' https://www.oracletutorial.com/oracle-s ... p_replace/, because the manual (pg 193) does not provide a clear understanding from my point of view of this command.

I consider this as SOLVED.

Thanks everyone,

Dream

Post by **Sliderule** » Sun May 31, 2020 10:49 pm

dreamquartz wrote:I understand your initial comment. That is where I had an issue with.
Dates are a totally different 'Beast'.

To anyone else reading this, except for dreamquartz, with EVERY database, any defined colomn as :

DATE
Time
TIMESTAMP

are a NOT different 'Beasts'.

Rather, they are defined as different data types and follow the rules accordingly. Different computer Built In Functions and stored as such.

A date is not a text string, nor, is a number text string, nor is a boolean field a text string etc. That is why they are defined differently.

Furthermore, if using HSQL as your database back-end, do NOT use the link above provided by user dreamquartz for REGEXP_REPLACE, since, this HSQL is not Oracle database, but, rather HSQL database, following the SQL standards.

dreamquartz · Post by **dreamquartz** » Mon Jun 01, 2020 8:29 am

@Sliderule,

Thank you for your kind words.

Your comments are always truly appreciated.

Dream

Post by **Sliderule** » Mon Jun 01, 2020 12:45 pm

dreamquartz wrote:@Sliderule,

Thank you for your kind words.

Your comments are always truly appreciated.

Dream

Since you have designated me as your Foe, your comments are always unappreciated.

Sliderule

chrisb · Post by **chrisb** » Tue Jun 02, 2020 8:57 pm

i dismantled the code posted by @Sliderule because i wanted to understand how the function 'REGEXP_REPLACE' impacted the result.
the use of 'REGEXP_REPLACE' may be valid but i discovered that its inclusion appears to be unnecessary.
it's possible that i am missing something & making a fool of myself yet again but i suspect the code could be amended as:-
HSQLDB 2.x:
input here is ('M YY' or 'MM YY')

Code: Select all

select m.*
from "MyTableName" m
where to_char(m."MyDateField", 'YYYY-MM')
	between to_char(to_date(replace(:From_MM_YY, space(1), '-' ) ,'MM-YY'), 'YYYY-MM')
		and to_char(to_date(replace(:To_MM_YY, space(1), '-' ) ,'MM-YY'), 'YYYY-MM')

OR modified to work with HSQLDB 1.8.0.10 (embedded):
unfortunately it seems necessary to input a four digit year like so('YYYY M' or 'YYYY MM')

Code: Select all

select m.*
from "MyTableName" m
where to_char(m."MyDateField", 'YYYY-MM')
	between to_char(cast(replace(:From_YYYY_MM, ' ', '-') || '-01' as date), 'YYYY-MM')
		and to_char(cast(replace(:To_YYYY_MM, ' ', '-') || '-01' as date), 'YYYY-MM')

Post by **Sliderule** » Tue Jun 02, 2020 9:15 pm

chrisb wrote:the use of 'REGEXP_REPLACE' may be valid but i discovered that its inclusion appears to be unnecessary.

The use of REGEXP_REPLACE insures the desired results even if any of the following is entered by an end user:

Code: Select all

'9/99'
'9-1999'
' 9/99'
' 9       99'
'    9                  1999      '
'9 20'
'9/20'
'9/2000     '
'9     20'
'9?20'

Therefore, any end user, or, salaried, or, paid for consultant can decide which is best for their use, or unnecessary.

Sliderule

chrisb · Post by **chrisb** » Tue Jun 02, 2020 9:25 pm

@Sliderule
thank you very much for the info, appreciated.
i really just wanted to make the point that your code could be adapted for use with embedded.

Post by **Sliderule** » Tue Jun 02, 2020 9:32 pm

The HSQL Embedded should NOT be used in any real world situation, whether for development or production.

HSQL, from Version 2.5.1 forward, will NOT convert a HSQL 1.8.0.10 AKA Embedded database to the latest version of HSQL.

Furthermore, the OP ( Original Poster ) of this forum did NOT post the database which database version is being used.

edit this topic's initial post · Post by **Villeroy** » Tue Jun 02, 2020 10:06 pm

2 of my 3 solutions work with embedded HSQL, one works file based connections after a minor adjustment.

dreamquartz · Post by **dreamquartz** » Wed Jun 03, 2020 4:58 am

@Sliderule

You have NEVER been my FOE.

Your solutions and suggestions are more than appreciated.
You are an invaluable to the community.
I always follow your responses to see if I can improve.

I can only say thank you very much.

I use HSQL 2.5.0

Works great for us and our Clients.

Dream

Post by **Sliderule** » Wed Jun 03, 2020 5:03 am

dreamquartz wrote:You have NEVER been my FOE.

Wrong, again.

Since 2017-11-20, you defined me as your FOE.

I will continue, and, never respond accordingly.

dreamquartz · Post by **dreamquartz** » Wed Jun 03, 2020 8:34 am

@Sliderule

Please elaborate....

I sent you a PM on: Mon Nov 20, 2017 11:39 pm.
That is the only communication I could find.

Again, Thanks for your hard work.

Really appreciate it.

Dream

[Solved] Date BETWEEN AND

[Solved] Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: Date BETWEEN AND

Re: [SOLVED] Date BETWEEN AND

Re: [SOLVED] Date BETWEEN AND

Re: [SOLVED] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND

Re: [Solved] Date BETWEEN AND