SQL Server datetime - Update and display issue

Hi All,

I'm a newbie in FM and I'm beginning to find my way in it but now I'm facing an obstacle I can't overcome.

I use a SQL Server database as db and FM as GUI.

All is working fine, I even found a workaround to the problem of the valuelists.

The problem I'm facing now has to do with the SQL Server 'DateTime' field. In FM there is no such field which causes a problem when a user needs to enter that field.

The user can't just enter a date but also needs to enter the 'time' part of the datetime field.

Is there a workarround for this? I searched the web but I could not find a solution for this.

I'm thinking of creating a popup window where the user selects/enters a date, add the timepart to the date with a script and assign this calculated value to the SQL field.

But this brings me to another problem: Displaying the date.

We have users in the US and Europe and the date format is different: US = mm/dd/yyyy and EU = dd/mm/yyyy.

To avoid confusion we would like to format the display to dd-MMM-yyyy (ex. 19-SEP-2008).

All the formatting I have tried is ignored. Why?

One solution could be to change the datetime field to a text field but then I need to create a (huge) validation on the field and this would not allow me to perform sorts a and finds on these fields ( 20-JAN-2000 will be considered earlier than 01-DEC-2008).

I'can use triggers on my SQL Serv er tables to copy these text dates to a shadow datetime field as this produces errors when updating the record.

:qwery: Does anyone have an idea on how to resolve this issue?

Thanks in advance

Edited September 26, 200817 yr by Guest