Replace Date fields with correct format DD/MM/YYYY

Currently - I have a few users in my database that are entering dates in the following format DD.MM.YYYY - FileMaker understands that this is a date however, I would like to create a script and also a Replace Field Contents with it in the following format DD/MM/YYYY - with the slashes.

The reason is I have problems importing this to SQL when the format is in dots(.), hypens(-).

Ideally, if I could convert all dates to DD/MM/YYYY that would be great.

I agree. Dates in FileMaker are a pain, but you appear to have a non-standard case too.

There are several approaches to this date problem, but probably the best one is to just write a custom function. The way I did it was just to re-arrange the parts of the date using the Position and other functions.

SQL is another issue.

Probably the slickest solution to the SQL conversion issue was posted by another user here on this forum. Here is that custom function. Basically it converts the SQL date to Japanese, which FileMaker does understand. Then it returns a Timestamp based on that. You could do other conversions after that.

Let ( [

theSQLTS = UnixTimeStamp ;

theFMTS = Substitute ( theSQLTS ; "-" ; "+" )

] ;

GetAsTimestamp ( theFMTS )

)

Or, you could create another custom function to re-arrange the SQL-returned date strings without the above intermediate custom function.

------

Once you like the custom function and you're sure you want to apply it to all of your date field(s) (to convert them all), you can use the REPLACE WITH CALCULATED RESULT. (Be careful with this one since it will replace all the fields' data.....always have a good and current backup before using these commands!)

I'm not sure, based on your initial posting, if you need to replace that Unix Timestamp field at all.

If you have a single date field with different formats in it, yikes!, that's a much more difficult problem. How would you know, for example, if 11/10/2016 is November 10 or October 11? I probably (hopefully) misunderstood what you were saying.

----

In my case, the SQL-Returned Timestamp field that FileMaker returns, surprisingly didn't work with FileMaker's own TimeStamp functions (that is, for date math) until I converted it using the Japanese function above.

Thus, without knowing the entire date-space of values you have (that is, every possible combination), i can't recommend one approach over another. Presumably, all the dates for a particular field have the same format.

Clearly, if you have different date formats, say, in different fields, you need to handle them differently.

I'd use a calendar picker for any date entry so users don't get a chance to enter dates in a way you want them, but you're probably talking about existing data here, anyway.