Jump to content
Salesforce and other SMB Solutions are coming soon. ×

ESS - SQL 2005 Data Type - DateTime


Recommended Posts



I built a database in SQL 2005 and added the table to my Filemaker 9 database using ESS. The DataType in SQL database is DateTime and when I view the DataType in Filemaker, it is coming through as a Timestamp.


I want my users to enter a date in the Filemaker (SQL) database by entering for example 1/25/2008 but I receive a Filemaker error stating this field must be a valid time timestamp (i.e. 01/25/2008 11:09:03 PM).


Is there a solution to this problem?





Link to comment
Share on other sites

Hi Greg,

What you've described is a limitation of the initial implementation of the ESS with respect to the handling of the MS-SQLS date-time format. FMI is aware of it, so perhaps we will see some enhancements in future releases, but for now, you have to work around it.


For display of DateTime, where what you require users to see is either a date or a time (not a timestamp), a relatively easy solution is to add supplemental fields (calcs) on the FileMaker side for display. The supplemental calc can readily convert the DateTime value from MS-SQLS to either date or time using:








The somewhat more tricky part is setting it up so users can input values that will be accepted by SQL Server as valid DateTime input. To do that, I suggest that you provide an edit interface with a global field (a date or time field in a utility table in your FileMaker file) into which the user can enter a new date, and a button or script that writes the entered value to the relevant field on the current SQL record. It can perform the required conversion using:




...which will put the value the user has entered into a global date field (assuming it is a valid date) into a format that can be accepted as a valid DateTime value in MS SQL Server. Depending on your interface, you may also want to have your update script clear the global field as it transfers the user-entered value to the current ESS record.


You could consider using a custom dialog, a pop-up edit window or a tab control to reveal the data entry (global field) and the button that triggers the transfer of the entered value to the server.


It's not ideal, but it's one of the limitations of the present implementation of ESS. FWIW, for the benefit of anyone else reading this, the issue discussed here is specific to MS-SQL 2000 and MS-SQL 2005. Other supported host (server) systems (Oracle, MY-SQL...) handle date and time formats a little differently and don't present this particular challenge.



Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use