I need some guidance in formatting a field that contains date & time into something FileMaker can sort. The data is from an existing MYSQL DB. The incoming field looks like this "31/7/2017 16:30:00" (DD/M/YYYY HH:MM:SS or DD/MM/YYYY HH:MM:SS).
I would like to be able to query on today's date and sort by date/time. When I try to do that as it is currently formatted coming from the MYSQL DB it sorts using the DD. I have it set as a timestamp.
I'm thinking that I will need to use a calculation to reformat the data into something Filemaker could use. I'm not to great at calculations. I've spent a good portion of my day researching online and referring to books trying to figure one out, but nothing is working. There is a couple of things I don't understand.
1. Does the time need to be in a separate field from the date?
2. When I put a calculation into auto-enter when defining a field, what triggers it to change the format for existing data?
3. I need assistance with the calculation itself.
Thanks in advance for any help with this.