SQL date incorrect

[Charity]

I do this

 

ExecuteSQL( "

SELECT " & GFN ( Preferences::FiscalDate) &

"FROM " & GTN ( Preferences::FiscalDate) 

; ""; "" )

 

The FiscalDate field now is date field with 9/27/2014.  But what comes out when I set a global variable with this calculation is 2014-09-27.  Strange.  I want 9/27/2014.  I think making it a variable makes it text and messes it up but I do not know how to change it.  I tried GetAsDate() but it did not work.

 

I do not know how to change the calculation.  I read variables are text only so I tried that but no luck either.  How to fix it please?

[Charity]

I need this because I plan to set a date field with it and use date calculations so I need a valid FileMaker date instead of this text date.

[eos]

That's a fine opportunity to meet some of the Text calculation functions and get your feet wet with text parsing …

Let (
  result = yourExecuteSQLCalculation ;
  Date ( 
    Middle ( result ; 6 ; 2 ) ; 
    Right ( result ; 2 ) ; 
    Left ( result ; 4 ) 
  )
)

SQL has similar functions, which comes in handy if your ExecuteSQL() returns not a single result, bur multiple rows; e.g.

 

"SELECT SUBSTR ( STRVAL ( theDateField ) , 6 , 2 ) + '/' + RIGHT ( STRVAL ( theDateField ) , 2 ) + '/' + LEFT ( STRVAL ( theDateField ) , 4 ) …"

 

Speaking of Preferences: why not use a global field in the first place (that you initialize from a normal field during startup)? Or even a Custom Function, which can be a quite convenient means to store what other environments call “Constants”.

[Charity]

Thank you Eos.  I will try it soon.

Oh so can you also tell me if I have uploaded a file to FMS13, it still remains on my desktop then sometimes I will be asked which version I want to open.  That really confuses me.  Am I supposed to delete the files on my desktop once I upload to server?

[eos]

then sometimes I will be asked which version I want to open.  That really confuses me.

 

Me too :-) Who or what is asking the question?

[Charity]

I am sorry I was not clear.  FileMaker.  I'll go to open one of my files which I also uploaded for testing on iPad.  And I'll begin to change things in the served version from my desktop signing in also but then when I go to open another FM file probably on my desktop, FileMaker gets confused and does not know which one to open and neither do I because I realise I probably have been making some changes in the served version and some changes in the local desktop version.  It makes me nuts.  I just read how to solve one part of that problem on host name so I know but FM does not show me that it just asks.

[eos]

You don't need to necessarily delete your other copy, but maybe compress it as a zip, so you cannot open it by accident, and you're clear on which version you're working (since now there is only the uploaded file to use).

 

Also remember that you need to open a hosted file via Open Remote (manually or scripted), not as a local file, even if you host it on your workstation.

 

But if you still have problems, it would be best to post this as a separate question/thread; no-one will find this under “SQL date” …

[Rick Whitelaw]

For my purposes using FMS allows me to always know which file is "the boss". Occasionally I copy the files off the server to my desktop machine, then open all the files and close them. I do this because closing a file on my desktop saves a copy to another location which is backed up by time machine. Since I'm the only user using multiple computers, I tend to do work on the live files on the server unless I have to do something very important which needs testing. Then I work on the desktop copy and upload the result once satisfied.