ExecuteSQL – Date Formats

Entry posted by John Sindelar April 21, 2012

2,301 views

https://fmforums.com/blogs/entry/341-executesql-%E2%80%93-date-formats/

The problem.

SQL, including the new ExecuteSQL calc function in FileMaker 12, returns dates in this format: YYYY-MM-DD. This can be a bit of a pain if you have a string of text like this and just want to transform the date part:1170 - 2012-03-09 - - Keep: Beta Presentation @ BRN Co1171 - 2012-03-15 - 08:30:00 - Call Arthur Murray to finalize data1172 - 2012-03-16 - 12:30:00 - Conference call with Melinda1173 - 2012-03-20 - 11:15:00 - @ Breakfast

The calc for the results above looks like this: ExecuteSQL ( “SELECT id, DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )

The solution.

Jason Young discovered that you can cast the dates as text in your results simply by concatenating them with a bit of text, so instead of using just the field DateStart in your query, you can use ” || DateStart to get this:1170 - 3/9/2012 - - Keep: Beta Presentation @ BRN Co1171 - 3/15/2012 - 08:30:00 - Call Arthur Murray to finalize data1172 - 3/16/2012 - 12:30:00 - Conference call with Melinda1173 - 3/20/2012 - 11:15:00 - @ Breakfast

The calc for those results looks like this: ExecuteSQL ( “SELECT id, ” || DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )

Yes, FileMaker supports concatenate in it’s select statements.

Hope that helps.