Let’s say I want to store dates in a table. If I create the date column in Servoy, it will be a datetime column, and searching for dates in a date-time column can be tricky. Does anyone see any problem with me going into the back-end db manager and changing the column type from date-time to just date? My initial tests suggest that it simplifies date searching considerably. Seems like a reasonable solution to the problem. Any downsides?
And if there are no downsides to that, then my next queston is, wouldn’t it be a good idea for Servoy to offer a plain date type? Or would that introduce db incompatibility problems?
I second that,
Not only finding dates but also sorting and subsummarising by dates is also quite tricky. Using a pure date field, and a time field would save a lot of work.
There is no problem in changing a datetime column to a plain date field.
If that’s what you want to do - then create your columns (and perhaps your tables as well) in PowerDesigner or some other tool - rather than the built-in Servoy editor.
The Servoy Dataprovider Editor is really just there as a courtesy - and is designed to work with all databases in the “lowest common denominator” way - including the datatypes.
You could just as easily argue that if you’re using Oracle the default should be CHAR and not VARCHAR…
Hi Bob
If that’s the case why not present the option for dates, time, datetimes as column types? It would be up to the developer to know/understand the limitations of there particular db backend.
If servoy handles this at least we know the installation/update of new solutions will be handled for us rather than having to tinker with backend tools all the time. This would be a major nuisance for deployment/upgrading vertical market apps.
Also, how about being able to set a column type as indexed or unique through the dataprovider tool. This can be done through db tools but again would be easier if servoy could handle it for us.
For the developer of a vertical market solution who wants to change the attributes of a column in all his clients’ dbs, couldn’t he have the solution send a one-time ‘ALTER TABLE’ command using the rawSQL plugin?
Hi Adrian
Rather than try to anticipate all the Datefield variations would it be helpful to just work with a claculated integer field ?
Date_yyyymmdd = (vDate.getFullYear() * 10000) + ((vDate.getMonth() + 1) * 100) + (VDate.getDate).
So today - 18July06 becomes 20060718.
Easy to find single days or ranges of dates, and monthly reports - 200607 - are simple as you do not need to find start/end dates. By extending the calc you could also include the time portion.
HTH
Regards
Graham Greensall
Worxinfo Ltd