1 Reply Latest reply on Aug 18, 2013 8:15 AM by philmodjunk

    Can't search by date

    ClarkeHill

      Title

      Can't search by date

      Post

           I create my database by exporting data from another application into a CSV formatted text file. Filemaker creates a DB out of that just fine, with all the fields as text fields. The field I want to search on is a date field formatted like "2013-08-14 16:00:12 +0000".

           Search for all dates less than that date while it is still a text field, such as "< 2013-08-15 15:15:45 +0000" doesn't work. 

      Converting it to a date field and searching gives me "no records match this criteria" when obviously many do.

      Converting it to a timestamp field and searching gives me "no records match this criteria" when obviously many do.

           If I convert it to date or timestamp it refuses to sort properly. No matter what I do, the records stay sorted in the order of another field, which is labeled "Created" from the csv file.

           How can I sort by date in these databases?

        • 1. Re: Can't search by date
          philmodjunk

               As you have discovered, you cannot evaluate a text field as a date or timestamp. Nor will getasDate or GetAsTimestamp work as the text you show is not in a format that FileMaker can recognize as a date or timestamp.

               The following expression can, however, extract a date from the field:

               Let ( [ T = YourFieldGoesHere ;
                        Td =  Leftwords ( T ; 1) ;  // extract the date as text
                        Y = Left ( Td ; 4 ) ; // year
                        M = Middle ( Td ; 6 ; 2 ) ; //month
                        D = Right ( Td ; 2 ) ] ; //day
                        Date ( M ;  D ; Y ) //use month ; day ; year to compute date
                      ) // let