ExecuteSQL – 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 Co
1171 - 2012-03-15 - 08:30:00 - Call Arthur Murray to finalize data
1172 - 2012-03-16 - 12:30:00 - Conference call with Melinda
1173 - 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 Co
1171 - 3/15/2012 - 08:30:00 - Call Arthur Murray to finalize data
1172 - 3/16/2012 - 12:30:00 - Conference call with Melinda
1173 - 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.

Featured Posts

Follow Along

Stay up to date with the latest news & examples from SeedCode

27 Comments

  • 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

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

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

      Hope that helps.

  • Dan

    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.

  • Jason

    Thanks, this worked like a dream for me

  • Kevin

    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

    • James

      Kevin, your method worked for me. I preferred it as I only needed a Date.

    • rully

      it work for me, thanks Kevin you save my time 😀

  • 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”.

  • Maida Sussman

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

  • BenG

    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.

  • 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.

  • 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

      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.

      • Ah, varchar…right! Was trying string and text.

        Thanks Jake!

        Sounds like the results are comparable to STRVAL. Not much value within FM as function results will be text anyway.

  • Bruce Robertson

    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.

  • jaketraynham

    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. 🙂

  • Bruce Robertson

    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.

  • Bruce Robertson

    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.

  • Bruce Robertson

    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.

  • jaketraynham

    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.

  • Bruce Robertson

    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. =)

  • 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.

Leave a Reply

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

Check out some of our other posts ...

Comments in FileMaker Calendar

Highlight Notes and Comments in Your Calendar

Inline Notes in DayBack Calendar The latest DayBack extension adds an icon to your events when there is a comment present that matches your criteria. You can

Closed Through the New Year

SeedCode is closed for our holiday break from December 19th through the end of the year. We’ll have folks available in case of emergencies and

COMPANY

FOLLOW ALONG

Stay up to date with the latest news & examples from SeedCode

© 2023 SeedCode, Inc.