ESS - SQL 2005 Data Type - DateTime

[grathvon99]

Hi,

 

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?

 

Thx,

 

Greg

[CobaltSky]

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:

 

GetAsDate(ESStable::YourESSDateTimeField)

 

or

 

GetAsTime(ESStable::YourESSDateTimeField)

 

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:

 

GetAsTimestamp(UtilityTable::gYourGlobalField)

 

...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.

 

HTH.

[grathvon99]

Thx,

 

That's what I thought I had to do, just was looking for a way not to use a button with a srript,smiley-smile

Thx,

 

Greg