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.
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.
Sounds like you want the Time() function, Tom. Like this:
Let ( ts = Get ( CurrentTimestamp ) ;
Time ( hour ( ts ) ; minute ( ts ) ; 0 )
)
Hope that helps.
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.
Thanks, this worked like a dream for me
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
Kevin, your method worked for me. I preferred it as I only needed a Date.
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”.
Loving this–you saved me lots of time on this today!
Brilliant! Thanks John and Jason!
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.
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/
Oops, it got moved…
http://fmforums.com/forum/topic/83765-solutions-sql-builder/
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!
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.
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.
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. 🙂
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.
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.
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.
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.
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.
Aha! Good to know, Thx! 🙂