SQL date filter

questions
1

Apologies, a bit brain-dead as this point in the afternoon.

I am selecting a date (will have no time portion), from a dropdown, and I want to use that to filter a SQL file where the date-times do have a time portion.

If I was providing that date as a constant in a SQL query, my WHERE clause would look like:

where date’’ = trunc(fil.date_added)

or

where to_date(‘<date_literal>’,‘yyyy-mm-dd’) = trunc(fil.date_added)

As I said, brain-dead. Cannot figure out how to get the date literal from clarion, whether stored as a date or as a string, into the filter I want to send. I think maybe I can construct the whole filter string and then use that, but otherwise I cannot figure out how to combine the bits I want to send directly in SQL and bits I want Clarion to put real values in, not literals.

2

You should be able to use date_part in your query as documented for PG.

3

Jon, another alternative is with ranges: date_added >= singleDate and date_added<singleDate+1
(inclusive on lower range and exclusive on upper range). Look which is better for the query plan

4

I’ve used either Frederico’s method or built the SQL as a string and passed that to the browse using SQL(sqlvar) in the filter.

5

An easy technique is to have a filter…

Where The:DateTime >= FilterDate AND The:DateTime < FilterDate + 1

6

Always for me when filter is not a simple equals.Can be mixed on different filter conditions.

7

WHERE CAST ([DATE_FIELD] AS DATE) =‘2017-08-16’
or if MSSQL
WHERE CAST ([DATE_FIELD] AS DATE) =DATEFROMPARTS(YEAR(CLA_DATE),MONTH(CLA_DATE),DAY(CLA_DATE))

8

I think I’ll go for building the whole string. Oracle is a bit weird about passing strings to represent dates. date’stringdate’ where it is in the format yyyy-mm-dd works fine, but straight ‘stringdate’ in the same format causes trouble, and what format the server expects in that second case varies depending on server settings. So building the whole string I think is the safest option

9

Despite the help, after another fruitless hour, I had to go for a work-around. I added a numeric field called “batch_added”, set that up with a key, and used a range limit. (Can’t do that with a date field because you can only select the string(8) field, and that clarion tries to send the data as a string).

Why did I have to give up? Between SQL() causing things inside it to be only totally literal, so that SQL(mylimitstring) would send “where mylimitstring…” to the server, getting messages about things not being bound, when they were, things getting unwanted quotations wrapped around them, I just could not get it to work. Plus just to complicate things this was a conditional filter on the fifth tab.

When I have some more time I will give it another spin, but I had to get the revision out, so…

10

OK, here are seven methods that don’t work. I haven’t found a method that does yet. In all cases the filter_string and the date_string are bound. This is the Oracle driver, though I don’t think it should make much difference. Date_string is just a string representing a date (like 2024-10-29) selected from a drop-down. As you would expect, where the filtering is done after the browse opens the unfiltered list appears before you try to filter it using the dropdown.

There are some things in there I cannot explain. For example, the SQL() doesn’t seem to recognize the closing bracket as the end of what it should take literally. I suspect the replacement of & with || (the concatenation operator in Oracle) is specific to Oracle.

I don’t know why the PROP:SQLFilter wraps stuff in quotation marks.

I suspect that the ones with the BIND errors are because it cannot figure out what to do with TRUNC and DATE when they are not wrapped in SQL().

I don’t know why some of them it decides to do a <> ‘’.

Method 2, the forum has translated the 80 character empty string back to a single character string.

I’m probably missing something blindingly obvious, but…

Jon

What I want to send to the server is:

…where trunc(dt) = date’2024-10-31’ order by dt

Method 1:
filter_string = ‘trunc(dt) = date’‘’ & date_string & ‘’‘’
browse filter = SQL(filter_string)
SQL sent: SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( filter_string ) ORDER BY A.DT
Result: Oracle 920 error (invalid relational operator)

Method 2:
filter_string = ‘trunc(dt) = date’‘’ & date_string & ‘’‘’
browse filter = filter_string
SQL sent: SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( ’ ’ <> ‘’ ) ORDER BY A.DT
Result: No error, no data

Method 3:
filter_string = ‘trunc(dt) = date’‘’ & date_string & ‘’‘’
browse filter – None
SetFilter: BRW1.SetFilter(‘SQL(filter_string)’)
SQL sent: SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( filter_string ) ORDER BY A.DT
Result: Oracle 920 error (invalid relational operator)

Method 4:

filter_string = ‘trunc(dt) = date’‘’ & date_string & ‘’‘’
browse filter – None
SetFilter: BRW1.SetFilter(filter_string) (also called after resetting the filter_string)
SQL sent: SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( A.DT >= :1 ) ORDER BY A.DT
Result: Clarion error “Bind has not been called…filter and range limits ignored”, all rows shown

Method 5:
Browse filter = SQL(trunc(dt) = date’) & date_string & SQL(‘)
or SQL(trunc(dt) = date’‘’) & date_string & SQL(‘’‘’)
Result: Clarion error “Invalid expression for evaulate…filter and range limits ignored”, all rows shown

Method 6:
Browse filter = SQL(‘trunc(dt) = date’‘’) & date_string & SQL(‘’‘’)
SQL sent: SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( ‘trunc(dt) = date’‘’ || ’ ’ || ‘’‘’ <> ‘’ )
Result: no data

Method 7:
filter_string = ‘trunc(dt) = date’‘’ & date_string & ‘’‘’
or 'trunc(dt) = date & date_string ’
brw1{PROP:SQLfilter} = filter_string
SQL sent: SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( A.DT >= :1 ) ORDER BY A.DT
Result: all data, no filter

11

Jon,

Change this to
browse filter = ‘SQL(’ & filter_string & ‘)’

12

[quote=“JonW, post:10, topic:7606”]
filter_string = ‘A.DT >= <39>’ & TheDateString ‘<39> AND A.DT < <39>’ & TheDateStringPlus1 & ‘<39>’

filter_string = ‘A.DT >= <39>’ & FORMAT(ClarionDate,@D010-) ‘<39> AND A.DT < <39>’ & FORMAT(ClarionDate + 1,@D010-) & ‘<39>’

13

Something like that would work OK in a limited fashion in this case. Why only limited? If I write:

select date'2024-10-29' dt
from dual

or

select to_date('2024-10-29','yyyy-mm-dd')
from dual

it will always work and return a date.

If I have a table with a date in it, and write:

select *
from bank
where mod_last_dt = '2024-10-29'

it will fail with a “literal does not match format string” error. This is because the database will interpret date strings using the locale setting, which in my case happens to be the American “dd-mon-yyyy” format. So if I modified your code to use the @d7- format, it would work on my server now, but there is no guarantee it would work anywhere else.

14

I really wanted that to work, but with the Oracle driver (I’ll try with the ODBC driver later), it produces:

SELECT A.ID, A.DT FROM TESTJON.“DATE_FILTER” A WHERE ( ‘SQL(’ || ’

15

Jon, can you show your actual code where you are setting the filter?

16

In Record Filter in Actions for the browse:

'SQL(' & filter_string & ')'

Reset field for the browse: date_field (the use of the dropdown box to select the date)

In Event Accepted on the dropdown:

filter_string = 'trunc(dt) = date''' & date_string & ''''

In BIND variables in ThisWindow.Init:

bind('filter_string',filter_string)
bind('date_string',date_string)
17

I tried with an ODBC connection (with postgresql as the backend) with similar results:

filter_string = date_trunc(day,dt) = date'2024-10-31'

filter on the browse still 'sql(' & filter_string & ')'

query sent to server:

SELECT A.ID, A.DT FROM public."date_filter" A WHERE ( 'SQL(' || '' || ')' <> '' ) AND ( A.DT >= ? ) AND ( ( A.DT > ? ) OR ( ( A.ID <= ? ) )) ORDER BY A.DT, A.ID DESC

18

If you add the DATE specifier will that work?

I’m pretty sure in MSSQL the yyyy-mm-dd format works regardless of the locale setting, and maybe Oracle does the same if you tell it the string is a date?

19

Absolutely, in the pure Oracle query situation having either date’stringdate’ or to_date(‘stringdate’,‘format’) will work just fine. My problem is that I cannot get Clarion to send the string I want to Oracle: it adds extra quote marks, only sends absolute literal text when you use SQL(), no Clarion variables, and adds incomprehensible additions like <> ‘’ where it shouldn’t. So, if the idea of using “between stringdate and stringdate + 1” is to simplify what Clarion sends so that it might actually manage it, if you need to add the date’’ wrapped around your date strings it doesn’t really simplify anything.

20

That’s interesting because I often use this syntax. I use Postgres and the local is Australia (dd-mm-yyyy’) but it always interprets the date if in ISO format.