AnsweredAssumed Answered

Extracting DATE from pseudo TIMESTAMP field?

Question asked by coh998 on Nov 14, 2013
Latest reply on Nov 14, 2013 by coh998


Extracting DATE from pseudo TIMESTAMP field?


     I have a recurring import of a .csv file that contains a TIMESTAMP in the format 2012-12-27 01:23:33. The Field Type is set to TIMESTAMP, but it does not sort ascending as expected unless I change it to TEXT. I suspect that the "-" characters are preventing Filemaker from recognizing and correctly sorting on the DATE info, since there is no option in either System Formats or DATE Formats that allow the use of that "-" character in a DATE format. I'm getting lots of "?" results or really weird Sort results depending on which Field Type is specified.

     Also, not all records contain timestamp data; many contain the text "NULL".

     At this time, I cannot Script or Calculate my way around the simplest problems, although I am trying to learn. But I'm hoping that someone here will provide a calculation or point me to docs on how to write one that returns "NULL" for those records where it is found and extracts a valid DATE of the format MM/DD/YYYY from the timestamp in such a way that the DATEs can be used in subsequent DATE RANGE calculations. I do not need the TIME data in this calculated field.