Use the ValueCount function to figure out how many are returned, pluck out the one you need (or loop through them) and use a custom function (or favorite method) to reformat the dates as required.
You then build a CR delimited list of the dates in the format you need.
Building on David's suggestion, here's a CF that accomplishes the task:
// Function name: cfUnixDateToDate ( input )
// Purpose: To convert a Unix-formatted date text string (such as returned by ExecuteSQL()) to an actual date value
// Author: Mike Mitchell, Net Caster Solutions, www.netcastersolutions.com
// Original publication date: 4/25/2013
Let ( [
inputNumeric = Filter ( input ; "0123456789" ) ;
inputYear = Left ( inputNumeric ; 4 ) ;
inputMonth = Middle ( inputNumeric ; 5 ; 2 ) ;
inputDay = Right ( inputNumeric ; 2 )
Case ( Length ( inputNumeric ) = 8 ;
Date ( inputMonth ; inputDay ; inputYear ) ;
Formatting in SQL
SELECT DISTINCT RIGHT(STRVAL(100+MONTH(dateCreated)),2)+'/'+RIGHT(STRVAL(100+DAY(dateCreated)),2)+'/'+STRVAL(YEAR(dateCreated))
very long expression, but not so slow (about x2 ?)
a bit shorter
SELECT DISTINCT SUBSTR(STRVAL(dateCreated),6,2)+'/'+RIGHT(STRVAL(dateCreated),2)+'/'+LEFT(STRVAL(dateCreated),4)
I often leave SQL formatting as is in the SQL report, but when I have a FileMaker field that I need to insert the SQL date, I set the FileMaker date field to Auto-Enter with the following calculation:
Let ( [
F1 = Table::DateFieldName ;
F2 = GetAsDate ( F1 ) ;
F3 = Substitute ( F1 ; "-" ; ¶ ) ;
F4 = Date ( GetValue ( F3 ; 2 ) ; GetValue ( F3 ; 3 ) ; GetValue ( F3 ; 1 ) ) ;
F5 = If ( F2 = "?" ; F4 ; F2 )
With this formula, if what you put in the date field is invalid, it then tries format it assuming it is in the SQL format and automatically converts it to the FileMaker format.
I've been away from the computer for a day. I used user19752's answer because it worked within my tooltip. The date field is a system entered creation date so it won't be blank or have an invalid value. But there will be many records, thus the "distinct" modifier. I'm liking ExecuteSQL more and more as I learn SQL. Thanks again for the help.
Since ExecuteSQL came out, there are few tables and scripts that I make that don't rely on SQL anymore. I am really happy FileMaker added this feature. The disadvantage is that fields are not dynamically linked to the FileMaker schema and will break if you do something like change a field name. Actually, now that I am hooked on SQL for FIleMaker... I keep finding myself wanting more like UPDATE, INSERT, DELETE, etc.
Scripts I understand.
Tables? Don't we still have some significant performance issues?
Maybe a little more detail about how you use the feature with tables?
Auto-enter calcs, I am guessing.
One of the big misconceptions many of us had, including me, was that SQL would be faster. SQL is just another interface for connecting to FileMaker and it does not in general improve performance. There are a few situations that due to how it access the FileMaker engine, it does go faster, but there are also times where it goes slower than traditional FileMaker processes.
I do use SQL in FileMaker calculations and it works just as well as normal FileMaker calcluations and I see no reason not to fully embrace it. It has the same performance penalty issues as other unstored calculations and in general, unstored calcs are best avoided, but sometimes needed.
Where I have found SQL the biggest improvement is in global variable reporting tables where no data is stored, it is all stored in a global variable. This in combination with Perform on Server has really increased the performance of some reports for my clients. The disadvantage is that it is not good for editing results. So for just reporting (or graphing), it is great.
Mark, lots of workarounds (and maybe what you want). I just wanted to remind you that you can have SQL make the "conversion" (and not complex as user19752 did - but the leading 0's are included in that calc!):
1. John Renfrew discovered that if you concatenate the date with "nothing", that if becomes the standard date format that we use (MM/DD/YYYY or DD/MM/YYYY or our non-Am cousins):
SELECT ''||dateCreated // note that's TWO single quotes before the two pipe characters
2. STRVAL() will convert to "text":
SELECT STRVAL(dateCreated) // again depending on where you are located in the world you get MM/DD/YYYY or DD/MM/YYYY
3. COALESCE() is a function that does several things. For dates:
SELECT COALESCE(dateCreated,'') // note the TWO single quotes after the comma
So using any of these should give your the column you want. I prefer the STRVAL() function.
Wow... Beverly! I didn't know about those. Already starting to change some scripts and use the STRVAL function. Thanks!
Indeed. I completely forgot about the concat, even though I use it. How awful is that?
Beverly, Thanks for the SQL tricks. Have you given any thought to expanding your "The Missing FM 12 ExecuteSQL Reference" to include more of these tips, tricks and workarounds? I'm reluctant to use any of the regular SQL books because I'm not sure if their syntax and explanations will work with FMP. And, I suppose that there is no guarantee that these tricks won't go away with a new version of FMP, unless they are really not tricks.
I've gotten a few new tricks and there are some additional ExecuteSQL functions in FMP13. Uh, maybe?
Come on Beverly, please? I'm a newbie who Taylor has recently hooked on SQL and I would love to see what else I can do with it?