relationship between dates

[Harjo]

I have a table1 with a date field1 which is filled in as: dd-MM-yyyy HH:mm

in an other table2 I have a date field2: dd-MM-yyyy (only date, not time)
in this table I want to place a portal (relationship) that shows all the related dates. relation between: table1_to_table2
(field1 and field2 are the key-fields)

I can't get it working! Anyone suggestions?

[Jan Aleman]

The easiest way to solve this is to use date only fields in both tables, if you need a time component on one of the sides create a calculation that stores the datetime field in a datefield:
datefield, calc:
return datetimefield;
The above will automagically truncate the time component.

At this point in time you cannot create date only fields from within Servoy (this is because not all databases have date only fields), use your favorite sql tool to do this directly on your backend database server and relaunch Servoy.

[Harjo]

Can you tell me how this can be done with Firebird?

Will this issue be possible in the future, in Servoy?

[Jan Aleman]

With firebird: use ibconsole or ibmanager to create a DATE field. Links to both tools:
http://forums.servoy.com/viewtopic.php? ... 0ibconsole

We are considering to support it in Servoy in the future. We are trying to avoid functionality in Servoy that is not supported by the most important backends our customers work on so it's a bit tricky.

[Harjo]

Is'nt there some kind of trick or workaround (yes, here is the Filemaker-thinking again ) to achieve this in Servoy now?

I think I don't need the a Date-only field. I need a function dat deletes the time-component.
I have seen that if a datetime field has the properties: dd-MM-yyyy and you create a new record and you fill in the date with the datechooser, that only the date is saved, not the time component. If I have this properties on both tables, the relation is working! It goes wrong when one/or both of the tables have the property: dd-MM-yyyy HH:mm



Is'nt there some function that cut's off the time-component? or to convert a date to text, so you'll get a unique link?
It's really important for ouer solutions, because we use a lot of this kind: -date-relations- and it would be nice, that it's working on all great/familiar database.

[Jan Aleman]

You could set your time to 00:00:00 to ensure the relation works with datetime fields. I don't think the formatting has any influence on that other than that if you format a date field dd-MM-yyyy and type in a date the time will be set to zero. If in another datetime field you use the creationdate the timestamp will be included so you will have to truncate it. For example like this:
myDateField = new Date(da.getFullYear(),da.getMonth(),da.getDate());

[Harjo]

If you look into the data in, for example, IBConsole, and you have a DATETIME-field with dd-MM-yyyy, you'll see that the date is: 16-08-2003 and not 16-08-2003 00:00:00.
So the time component is not set to 0, but is gone!

I will try what you suggests.

[Harjo]

Thanks Jan, the truncate method works!
All you have to do is to make an extra field, thats truncates the time component. So you don't have to use a real DATE-field,
This way you can use date-relations with TIMESTAMP-fields.