Convert Date from SQL to FM Syntax

[Phillip Prahl]

Hi,

I have the a calculation field running the following:

ExecuteSQL ("SELECT MIN(checkins.date_due) FROM checkins  WHERE checkins.id_contact=? AND checkins.isComplete=0"; ""; ""; id)

If I tell the field that the calculation result is a number, then I appear to get the correct date back in the following format: YYYYMMDD, otherwise I get nothing or a ?.

However using this result in a comparison with say get(CurrentDate) does not work.

The strange thing is, that if I set the data formatting in the inspector to be 'general' i get YYYY-MM-DD, so it must know its a date somehow?

Any help about how to get fop to recognize the sql result as a fmp date would be greatly appreciated!

 

[beverly]

Try this:

STRVAL( MIN(checkins.date_due) )

or 

COALESCE ( MIN(checkins.date_due), '' )

These "convert" to FMP -date format of mm/dd/yyyy (or dd/mm/yyyy if non-US).

 

 

[beverly]

Sorry, "nested functions" (if one is an aggregate) do not seem to work well in eSQL. try this:

Let (
    [ $query = " SELECT MIN(checkins.date_due)
        FROM checkins 
        WHERE  checkins.id_contact=? AND checkins.isComplete=0"

    ; $result =ExecuteSQL ( $query 
        ; "" ; "" 
        ; id ) // returns yyyy-mm-dd

 

    ]; If ( $result ≠ "?" ; 
     
        Let ( 
            [ $y = Left($result; 4)
            ; $m = Middle($result; 6; 2) 
            ; $d = Right($result; 2) 
            ]; Date($m;$d;$y ) 
            )
            
        ; "?" )
) 

[Phillip Prahl]

Thank you so much!

I was also in the process of trying a workaround where I simply have another field that stores the date as an integer and then do my sql query with this field...

Will try both solutions and see what happens

Thanks again!

[Phillip Prahl]

Your suggested solution works great! Thanks for "let"ting me know