ExecuteSQL – Date Formats
· 2,645 views
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.
Recommended Comments