Filter Date Issue

D

danoshea

Member
I'm trying the Pre-Filter for the first time and I am trying to display new boat listings from the prior 7 days.

When I use WHERE "Date Listed" CONDITION "greater than" and VALUE "now()- INTERVAL 1 WEEK"

It returns no records. When I change it to "less than" I get all records.

Is it a field type issue or what?

Thank you
 
Yes, precisely used the example of that link you gave me, which is why I was wondering if the field type in Elements should be left at "Field".
 
Silly question, but: do you have records in this period?

It's working on my site.
You can enable "Fabrik debug" in Fabrik Options/Debug
and then append &fabrikdebug=1 to your URL (or ?fabrikdebug=1 if it's the 1st URL parameter)
A click on ListGetData will show the generated query.
 
No not silly, I'm sure you get them all the time. :)

Anyway, my dates go back from 08/17/14 to 07/01/14 and the cells are in date format.

I enabled Debug and here is my query:

SELECT SQL_CALC_FOUND_ROWS `swyachts`.`ImgPr` AS `swyachts___ImgPr`, `swyachts`.`ImgPr` AS `swyachts___ImgPr_raw`, `swyachts`.`DateList` AS `swyachts___DateList`, `swyachts`.`DateList` AS `swyachts___DateList_raw`, `swyachts`.`DateMod` AS `swyachts___DateMod`, `swyachts`.`DateMod` AS `swyachts___DateMod_raw`, `swyachts`.`DateSold` AS `swyachts___DateSold`, `swyachts`.`DateSold` AS `swyachts___DateSold_raw`, `swyachts`.`BestBuys` AS `swyachts___BestBuys`, `swyachts`.`BestBuys` AS `swyachts___BestBuys_raw`, `swyachts`.`Featured` AS `swyachts___Featured`, `swyachts`.`Featured` AS `swyachts___Featured_raw`, `swyachts`.`Status` AS `swyachts___Status`, `swyachts`.`Status` AS `swyachts___Status_raw`, `swyachts`.`VssNm` AS `swyachts___VssNm`, `swyachts`.`VssNm` AS `swyachts___VssNm_raw`, `swyachts`.`VssNmPrv` AS `swyachts___VssNmPrv`, `swyachts`.`VssNmPrv` AS `swyachts___VssNmPrv_raw`, `swyachts`.`VssNmLnch` AS `swyachts___VssNmLnch`, `swyachts`.`VssNmLnch` AS `swyachts___VssNmLnch_raw`, `swyachts`.`OwnNmCur` AS `swyachts___OwnNmCur`, `swyachts`.`OwnNmCur` AS `swyachts___OwnNmCur_raw`, `swyachts`.`OwnNmPrv` AS `swyachts___OwnNmPrv`, `swyachts`.`OwnNmPrv` AS `swyachts___OwnNmPrv_raw`, `swyachts`.`OwnNmLnch` AS `swyachts___OwnNmLnch`, `swyachts`.`OwnNmLnch` AS `swyachts___OwnNmLnch_raw`, `swyachts`.`Pdf` AS `swyachts___Pdf`, `swyachts`.`Pdf` AS `swyachts___Pdf_raw`, `swyachts`.`Html` AS `swyachts___Html`, `swyachts`.`Html` AS `swyachts___Html_raw`, `swyachts`.`VT` AS `swyachts___VT`, `swyachts`.`VT` AS `swyachts___VT_raw`, `swyachts`.`GA` AS `swyachts___GA`, `swyachts`.`GA` AS `swyachts___GA_raw`, `swyachts`.`YachtID` AS `swyachts___YachtID`, `swyachts`.`YachtID` AS `swyachts___YachtID_raw`, `swyachts`.`VssNmSite` AS `swyachts___VssNmSite`, `swyachts`.`VssNmSite` AS `swyachts___VssNmSite_raw`, `swyachts`.`BaseCat` AS `swyachts___BaseCat`, `swyachts`.`BaseCat` AS `swyachts___BaseCat_raw`, `swyachts`.`SubCat` AS `swyachts___SubCat`, `swyachts`.`SubCat` AS `swyachts___SubCat_raw`, `swyachts`.`PrListOrgUS` AS `swyachts___PrListOrgUS`, `swyachts`.`PrListOrgUS` AS `swyachts___PrListOrgUS_raw`, `swyachts`.`PrListOrgEUR` AS `swyachts___PrListOrgEUR`, `swyachts`.`PrListOrgEUR` AS `swyachts___PrListOrgEUR_raw`, `swyachts`.`PrListCurUS` AS `swyachts___PrListCurUS`, `swyachts`.`PrListCurUS` AS `swyachts___PrListCurUS_raw`, `swyachts`.`PrListCurEUR` AS `swyachts___PrListCurEUR`, `swyachts`.`PrListCurEUR` AS `swyachts___PrListCurEUR_raw`, `swyachts`.`PrChgUS` AS `swyachts___PrChgUS`, `swyachts`.`PrChgUS` AS `swyachts___PrChgUS_raw`, `swyachts`.`PrChgEUR` AS `swyachts___PrChgEUR`, `swyachts`.`PrChgEUR` AS `swyachts___PrChgEUR_raw`, `swyachts`.`PrPctChgUS` AS `swyachts___PrPctChgUS`, `swyachts`.`PrPctChgUS` AS `swyachts___PrPctChgUS_raw`, `swyachts`.`PrPctChgEUR` AS `swyachts___PrPctChgEUR`, `swyachts`.`PrPctChgEUR` AS `swyachts___PrPctChgEUR_raw`, `swyachts`.`PrSldUS` AS `swyachts___PrSldUS`, `swyachts`.`PrSldUS` AS `swyachts___PrSldUS_raw`, `swyachts`.`PrSldEUR` AS `swyachts___PrSldEUR`, `swyachts`.`PrSldEUR` AS `swyachts___PrSldEUR_raw`, `swyachts`.`PrChgSldPctUS` AS `swyachts___PrChgSldPctUS`, `swyachts`.`PrChgSldPctUS` AS `swyachts___PrChgSldPctUS_raw`, `swyachts`.`PrChgSldPctEUR` AS `swyachts___PrChgSldPctEUR`, `swyachts`.`PrChgSldPctEUR` AS `swyachts___PrChgSldPctEUR_raw`, `swyachts`.`DOM` AS `swyachts___DOM`, `swyachts`.`DOM` AS `swyachts___DOM_raw`, `swyachts`.`LOA` AS `swyachts___LOA`, `swyachts`.`LOA` AS `swyachts___LOA_raw`, `swyachts`.`Model` AS `swyachts___Model`, `swyachts`.`Model` AS `swyachts___Model_raw`, `swyachts`.`YrBlt` AS `swyachts___YrBlt`, `swyachts`.`YrBlt` AS `swyachts___YrBlt_raw`, `swyachts`.`YrRft` AS `swyachts___YrRft`, `swyachts`.`YrRft` AS `swyachts___YrRft_raw`, `swyachts`.`Builder` AS `swyachts___Builder`, `swyachts`.`Builder` AS `swyachts___Builder_raw`, `swyachts`.`YrdNu` AS `swyachts___YrdNu`, `swyachts`.`YrdNu` AS `swyachts___YrdNu_raw`, `swyachts`.`GstStRms` AS `swyachts___GstStRms`, `swyachts`.`GstStRms` AS `swyachts___GstStRms_raw`, `swyachts`.`GstSlp` AS `swyachts___GstSlp`, `swyachts`.`GstSlp` AS `swyachts___GstSlp_raw`, `swyachts`.`CrwQrt` AS `swyachts___CrwQrt`, `swyachts`.`CrwQrt` AS `swyachts___CrwQrt_raw`, `swyachts`.`CrwBrth` AS `swyachts___CrwBrth`, `swyachts`.`CrwBrth` AS `swyachts___CrwBrth_raw`, `swyachts`.`CrwSlp` AS `swyachts___CrwSlp`, `swyachts`.`CrwSlp` AS `swyachts___CrwSlp_raw`, `swyachts`.`Beam` AS `swyachts___Beam`, `swyachts`.`Beam` AS `swyachts___Beam_raw`, `swyachts`.`Engines` AS `swyachts___Engines`, `swyachts`.`Engines` AS `swyachts___Engines_raw`, `swyachts`.`GRT` AS `swyachts___GRT`, `swyachts`.`GRT` AS `swyachts___GRT_raw`, `swyachts`.`Helo` AS `swyachts___Helo`, `swyachts`.`Helo` AS `swyachts___Helo_raw`, `swyachts`.`Elvtr` AS `swyachts___Elvtr`, `swyachts`.`Elvtr` AS `swyachts___Elvtr_raw`, `swyachts`.`CityState` AS `swyachts___CityState`, `swyachts`.`CityState` AS `swyachts___CityState_raw`, `swyachts`.`Country` AS `swyachts___Country`, `swyachts`.`Country` AS `swyachts___Country_raw`, `swyachts`.`Agreement` AS `swyachts___Agreement`, `swyachts`.`Agreement` AS `swyachts___Agreement_raw`, `swyachts`.`BrokerInfo` AS `swyachts___BrokerInfo`, `swyachts`.`BrokerInfo` AS `swyachts___BrokerInfo_raw`, `swyachts`.`Details` AS `swyachts___Details`, `swyachts`.`Details` AS `swyachts___Details_raw`, `swyachts`.`YachtID` AS slug , `swyachts`.`YachtID` AS `__pk_val` FROM `swyachts` WHERE ( swyachts.DateList > now()- INTERVAL 1 WEEK ) ORDER BY `swyachts`.`DateList` DESC
 
Glad you got it sorted. Yes, any date manipulation you do in a query using MySQL date functions will require the date to be stored in standard MySQL format. If you use a Fabrik 'date' element, they will already be formatted that way in the table, regardless of how you display them in the form / list.

If you don't use a date element, you will need to handle the format conversion yourself.

-- hugh
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 109 (members: 3, guests: 106)
    Back
    Top