frustration over date handling

[DFehrenbach]

I have been using Servoy now for about 3 months and I feel I am still "in the dark" with regard to date handling issues. I have poured over the Developer and Advanced Filemaker documentation on the subject and end up even more frustrated because the information is scattered and the examples I find are less than complete.
I guess what I am looking for are fundamentals of how to represent dates and a "best practices" form of advice on how date and time issues should be handled, how to use and format calculated dates, when to use JS Lib Date routines and when to use utils date routines, when dates "need" to be formatted as text and when they need to be in DATETIME format.
If someone has a bit of spare time (yeah! right! ) could they please tell me how they handle such issues? I would be most appreciative.

[bcusick]

Where are you confused? What better examples can we provide?

Here's some guidelines that I use (personally - I'm sure there are better ways - but this is what I do):

1) REMEMBER - "dates" in SQL are dates AND TIMES - and not separate (although if you use a backend database manager - you can set the default datatype of datetime to date [only]). This means that when you search for a single date - you must include the TIME increment or use the "#" before your query.

Example: searching for today's date in a column called "order_date":

//this populates the variable today with the current date AND TIME:
var today = new Date()

controller.find()

//we just want to search the DATE - so strip off the "time"
order_date = "#" + utilities.formatDate(today, 'M/d/YYYY') + "|M/d/YYYY"

controller.search()

Here's what happening above: the "#" indicates that the sql should look for the TIME 00:00...23:59:59 - or in other words - the date given but EVERY HOUR within that date as well. The formatting and the "|M/d/YYYY" tell the SQL parser what date format the data will be in. Also see: http://forum.servoy.com/viewtopic.php?p=1732 and http://forum.servoy.com/viewtopic.php?p=7212#7212

You can avoid this hassle by simply setting the date field to a "DATE" datatype (in the backend - using the backend tools - this CANNOT be done via the Servoy interface).

Date and time issues can be handled in a wide number of ways - it depends on what you're trying to accomplish. Do you have a specific example?

How to use and format calculated dates ?? Sorry, don't know what this means. If you're trying to ADD dates together - use the JS Lib Date routines and convert them to SECONDS first, then convert that calculated value back to a date/time. See: http://forum.servoy.com/viewtopic.php?p=7212#7212

Date only "need" to be formatted (or changed from their JavaScript date OBJECT into a string or numeric value) when you're calculating or searching. You can use either the "utilities" or the JS Lib - depending on what you need.

Again, if you can provide some more specific examples, perhaps I can be of more help.

Cheers,

Bob Cusick