query using between date

[juned_seskoad]

I want to make FPL script to make a query filter with Date Field

I have data source using date field name "DATEX", on where clause (assisted SQL) I add &&WHERE_Q. Then on Resource Field I add WHERE_Q (Dynamic sql) and DATEX_Q (VARCHAR)

then I make FPL script :
set DATEX_Q = DATEX_Input_from + 'AND' + DATEX_Input_to;
set WHERE_Q = ' DATEX BETWEEN DATEX_Q ';

But it not working, please help me to make a query filter using between date

Regards,

Juned

[Jon]

Juned,

I think I would do it like this - a bit different from your approach:

1. Add two new resource fields to your Database Resource: from_time and to_time. Set the types to TIMESTAMP or DATE (whatever matches the database column type you want to compare against). Uncheck the Persistent column for both (very important) - this stops the system trying to read these columns from the database.

2. Set the Where clause to be:

Code: Select all

time_column between &&from_time and &&to_time

where time_column is the table column that you are checking.

3. Add two new form fields to your form from_time and to_time. Set the types to DATETIME (if DB column is TIMESTAMP) or DATE (if DB column is DATE).

4. Click the Resource Mappings icon on the form toolbar (top of the form editor) and map the new form fields to the resource fields of the same name.

5. Set values for from_time and to_time before you issue fetchtable e.g. using FPL and fields of type DATETIME:

Code: Select all

to_time = $SYSTEM_DATETIME;                    // now
from_time = $SYSTEM_DATETIME;
from_time = from_time - (10 * 24 * 3600);    // 10 days ago
fetchtable mytable;

Regards
Jon

[juned_seskoad]

Thanks Jon for your reply, it work.

Regards

Juned

[alexmcclune]

I have also been struggling with this in Javascript and I am struggling a little. I have previously used date's in insert/update/select without problem until now.

My query is built using native_sql as I am required to use UNION in my syntax. Each select statement includes a where clause of between &FRM_DATE_FROM and &&FRM_DATE_TO. These values are populated from two form fields of type Date and these are mapped to the database resource fields (dynamic and not persistent). The fetchtable command is executed from a button only when both fields are input (via the calendar). Every time I try and run the query it fails with the error:

Code: Select all

Cannot set property "value" of undefined to "1409526000000" 

When viewing the error I find the query has been built as:

Code: Select all

01/09/2014 AND logged_date < 30/09/2014 

I know about the date object in Javascript and I have been trying this unsuccessfully (usually states there is a datatype mismatch or some such error). The following error occurs if I try and set the date's manually to '1900-01-01 'which works if I run it against my database:

Code: Select all

String, value '1900-01-01' not compatible with field type DATE 

[Segi]

Alex,

I've found that when you are building a query and working with dates, you need to do refer to the displayValue property of the field/table resource instead of value since value is equal to the number of seconds since 1/1/1970

[Jon]

Cannot set property "value" of undefined to "1409526000000"

Undefined implies that you have a scripting error e.g. if I have:
fields.XXX.value = new Date();
and field XXX doesn't exist, I would expect this error.

In general with dates, Ebase fields of type DATE/TIME/DATETIME should work quite happily with almost all database column types supporting dates and times (there are one or two exceptions to this where the database system has introduced new types). You should not need to manipulate these in any way.