Jump to content

Convert Date from SQL to FM Syntax


This topic is 3088 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

 

Link to comment
Share on other sites

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 ) 
            )
            
        ; "?" )

  • Like 1
Link to comment
Share on other sites

This topic is 3088 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.