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:

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:

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.

Tagged with →  

27 Responses to ExecuteSQL – Date Formats

  1. Aha! Good to know, Thx! :)

  2. mac24x7 says:

    As usual, John Sindelar comes up with the goods. He is without a doubt, one of the most giving people in the FileMaker community. Thanks again, John.

  3. Bruce Robertson says:

    Perhaps something happened to the SQL statement when you posted it? It doesn’t seem to work. Can you post a corrected example?

    • Hey Bruce, Works over here. I think the issue might be that the two characters before the double pipes (before the concatenation characters) are two single quotes. The original idea was to place a single space (any text really) in front of the date like this ‘ ‘ || DateStart to get FMP to see the date as text. Turns out you don’t even need the space so we went to ” || DateStart. It’s either that or your fields / table aren’t the same as ours. =)

  4. jaketraynham says:

    Whenever I see a double pipe, I think “or”. Of course, I’m a c++ programmer, but javascript programmers might think that to. Anyway, you can also just use a + to concatenate .. might make more sense to some people.

  5. Bruce Robertson says:

    Thanks for the explanation John. If you try copying the ExecuteSQL statement from above, it does not contain single quotes, at least when you paste it into the data viewer. Putting in the single quotes works.

  6. Bruce Robertson says:

    Actually the + concatenate operator doesn’t work, at least for this example. It may be a sequence of operations thing. The result entirely drops the date. Using the single quotes with no space and John’s SeedCodeCalendarFree.fmp12 example file:

    ExecuteSQL ( “SELECT id, ” + DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )
    Result:
    Ev0004400 – – – Beta Presentation @ BRN Co
    Ev0004401 – – 08:30:00 – Call Arthur Murray to finalize data being imported for the HW Beta.

    Only if the single quotes are used, together with pipes, do you get the date as text.
    ExecuteSQL ( “SELECT id, ” || DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )
    Ev0004400 – 3/27/2012 – – Beta Presentation @ BRN Co
    Ev0004401 – 3/27/2012 – 08:30:00 – Call Arthur Murray to finalize data being imported for the HW Beta.

    Back to + operator but value of zero:
    ExecuteSQL ( “SELECT id, ‘0’ + DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )
    Result (dates unchanged):
    Ev0004400 – 2012-03-27 – – Beta Presentation @ BRN Co
    Ev0004401 – 2012-03-27 – 08:30:00 – Call Arthur Murray to finalize data being imported for the HW Beta.

    + operator and numerical value:
    ExecuteSQL ( “SELECT id, ’10’ + DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )
    Result: dates incremented by 10 days.
    Ev0004400 – 2012-04-06 – – Beta Presentation @ BRN Co
    Ev0004401 – 2012-04-06 – 08:30:00 – Call Arthur Murray to finalize data being imported for the HW Beta.

  7. Bruce Robertson says:

    In all cases above a pair of singe quotes was what I entered but the web form doesn’t accept them and does a substitution.

  8. jaketraynham says:

    After playing with this a bit more, I understand it a bit more. The + operator appears to try to actually do an “add” operation which depending on the underlying type does different things. If you use + with two strings, it concatenates them. If you use + with two numbers or a number and a date, it adds them mathematically. If you use + with a string and a number, it seems it doesn’t know what to do and returns nothing. I’m kind of surprised it doesn’t return a type mismatch error like some other operations do.
    On the other hand, the || operator appears to always typecast the underlying types to strings and then concatenates them. One interesting thing I found with this is when it does the typecast to a string, it’s actually using whatever text was originally typed into the field. In other words, if you typed “04/22/2012” into your date field, it would return that. If you typed “4-22-2012” in your date field, it would return that. Similarly, if you type “asdf” into a number field, it would return “asdf”, but referencing the number field by itself would return nothing (because it’s not a number).
    So, yea .. interesting stuff. :)

  9. Bruce Robertson says:

    Yes, interesting indeed. ExecuteSQL may end up making us all gain a bit of new appreciation for clunky calc editor dialogs with their immediate feedback.

  10. The way we stumbled upon this was trying to concatenate a Date with Text. This fails, and throws an ‘incompatible data type’ error. However, if you concatenate Text with Date it works as Jake describes, i.e. you get “As Entered.” Very nice since CAST does not seem to be supported, and STRVAL still returns dates as yyyy-mm-dd.

    We also couldn’t find the Concatenation Operator references anywhere for FMSQL. I was surprised that || is the one that works, as I associate that one with Oracle?!? Go figure!

    • jaketraynham says:

      FMSQL does support cast (in the form of “cast(mydate as varchar)”), but it’s mostly useful for setting values or with where clauses when you’re having to massage user-entered data. However, cast also returns yyyy-mm-dd, so your trick is still valid and useful.

  11. Eden Morris says:

    Thanks for the tip! I have incorporated this into a database I’m working on that I use to generate my SQL commands for FileMaker. If anyone would like to use it and give feedback here is where you can find it.

    http://fmforums.com/forum/topic/83726-sql-builder/

  12. sumware says:

    Nice trick.

    Do remember that the date, when cast as text is returned in the date format the database was created/cloned in. That might, in some circumstances, give unexpected results in different parts of the world.

  13. BenG says:

    Interesting. If you do
    “Select Cast(DateStart as varchar) from SampleEvents” it stays as YYYY-MM-DD so it must be doing something in addition to casting. In the past using odbc to connect to FileMaker I have used a concatenation like below..:

    select Substring(Cast(a.DateOrTimeStampField as varchar ),6,2) +’/’ + Substring(Cast(a.DateOrTimeStampField as varchar ),9,2) +’/’ +
    Left(Cast(a.DateOrTimeStampField as varchar ),4)
    from MyTable a

    Although this looks convoluted this might be safer to use as I would think the cast function should return the same output as your ” || datefield technique.

  14. Brilliant! Thanks John and Jason!

  15. Maida Sussman says:

    Loving this–you saved me lots of time on this today!

  16. Steve Cooper says:

    Thanks for the ideas!

    I have been playing around & found that “LEFT ( MONTHname ( DateStart ) , 3 ) || ‘ ‘ || DAY ( DateStart ) || ‘ ‘ || YEAR ( DateStart )” will return the date in a visual format – “Nov 17 2011”.

  17. Kevin says:

    Here is another way which is slight different to this thread but if you run the sql date result through this function it converts it to filemaker date format. I have no idea why it works it just does.

    GetAsDate( Substitute( “2009-11-30” ; “-” ; “+”)) gives you 30/11/2009

  18. Jason says:

    Thanks, this worked like a dream for me

  19. Dan says:

    Be warned that attempting to use functions like MIN and MAX with this trick in order to return formatted dates will return incorrect results. Rather than look at the minimum or maximum date value, it will look at the first number in the string, and then return the minimum or maximum based on how those numbers would be ALPHABETICALLY sorted, not numerical. Hence, weird results like 11 being less than 2, etc.

  20. Hi, your example above does not show how to remove the seconds in the time ?? when I try to concatenate my Hour ( ) and Minutes ( ) … Hour ( timestamp ) +”:”+Minutes ( timestamp ) I get 7 + 12 or 19 instead of what I should see 7:12. When I use them Independantly; Hour ( timestamp ) yields 7 and Minute ( timestamp ) yields 12. So how do I put them together with a “:”. Any suggestions ? I would be grateful. Thank you.
    Thank you.

    • seedcode says:

      Sounds like you want the Time() function, Tom. Like this:

      Let ( ts = Get ( CurrentTimestamp ) ;
      Time ( hour ( ts ) ; minute ( ts ) ; 0 )
      )

      Hope that helps.

Leave a Reply

Your email address will not be published. Required fields are marked *

Share →
DOWNLOAD
DayBack Calendar
DayBack's 30-day trial is unlocked so you can customize it and integrate it with your files.
Download DayBack and we'll send you a couple short emails with tips on how to modify it and use some of the coolest features.
Thank you! Please download: DayBack Calendar
Need More?
SeedCode tips & example files in your inbox
Need More?
SeedCode tips & example files in your inbox
Want More?
Be the first to see articles and tips like these
DOWNLOAD
TimeZync
Download TimeZync and we'll send you a couple short emails with tips syncing your FileMaker Go files.
Thank you! Please download: TimeZync
Want More?
Be the first to see articles and tips like these