Search criteria and timestamp field

robleer

Compared to the 4.x version, in the 5.x version it is possible to make more than one filter on fields.

I've made such a filter on an timestamp field.

But when I enter search criteria like '01-12-2011' or '01-12-2011 12:00' the system never returns any matching records.

I tried this on a Derby database btw

Had this something to do with the locale settings of my datetime format?

It also would be nice if there was a calendar button on date search criteria.

Any clue?

rocketman

Try -

Find xx where xx.timestampattribute = TIMESTAMP(1,12,2011,12,0)

For further details see the TIMESTAMP funtion in the how to user manual

Function:
TIMESTAMP
Description:
Returns the timestamp constant built from the given numbers
Parameters:
An integer constant indicating day (1-31), an integer constant indicating month (1-12), an integer constant indicating year, an integer constant indicating hours (0-23), an integer constant indicating minutes (0-59)
Example:
If TIMESTAMP (DAY_OF_MONTH (Account.OpeningDate), 4, 2004, 11, 0) = 07/04/2004 11:00 Then …

rocketman

Ps ... If you're just looking to match dates try

Find xx where DATE_PART(xx.timestampattribute) = '01-12-11'

robleer

Hi Rocketman,

Sorry, I mean something else.

In a query you can add 'filters'. They show up above the query results and then you can add extra search criteria.

If you add a filter on a timestamp field, it does not work.

Hope this explains the problem a little more..

rocketman

have you tried it without the commas ie just 01-12-2011

rocketman

I can confirm date searches work if you do not enclose the dates in quote marks

Also, If the date field is included in the query details, and you have "show reduce/extend search button" ticked, if you click the binoculars, you'll get a calendar icon next to the date fields

robleer

I do not mean the Reduce/Extend screen.

When designing the query you can add filters which appear above of underneath the query results. It a small button called 'Filters' next to the 'Grouping' button.

This does not work on a Timestampfield (not a date field). It has nothing to do with quotes or not.