Jump to content
  • entries
    146
  • comments
    3
  • views
    78,988

ExecuteSQL – Date Formats

Sign in to follow this  
John Sindelar

1,353 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.

735 735 735 735 735 735 735 b.gif?host=seedcodenext.wordpress.com&bl

Source

Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.