TIME data in DATETIME fields problem

Classic Servoy
1

The TIME part passed to DATETIME FIELD is a real source of problems.

After a couple of hours of testing (I thought it was a problem related to saving datas), I figured out that creating an appointment via a relationship also sets the current time to the appo_date field.
Not a big issue, unless you have to manage the result of a query, that fails if you search this date.
A SQL query like: SELECT appo_date, operatorid from appointments where appo_date = “2004-08-18” returns nothing if you have a record with appo_date containing a value like ‘2004-08-18 19:30:28.743’.

I manage to set the time values to zero (even the milliseconds cause problems) using a method, but it’s a very counterintuitive behaviour and I think it would be great if you could do something to change it. I mean: no time values passed by default to datetime fields. :wink:

2

A # before the date you are looking I think will solve your problem:

SELECT appo_date, operatorid from appointments where appo_date = “#2004-08-18”

more on:

http://forum.servoy.com/viewtopic.php?t=1732
http://forum.servoy.com/viewtopic.php?t=1775

ciao

3

automazione:
A # before the date you are looking I think will solve your problem:

SELECT appo_date, operatorid from appointments where appo_date = “#2004-08-18”

I don’t think it works with Sybase: it gives me an error in Servoy and testing it in InteractiveSQL the error message tells thant “cannot convert #2004-08-18 to a timestamp”

4

Hi Riccardino,

I tried:

controller.find()
datafield  = "#" + utils.dateFormat(globals.datax, "d.M.yyyy") + '|d.M.yyyy';
controller.search()

where data_field is the data field where to search and datax a global containing the data to search for. Servoy does the sql translations and everything works fine.

Also tried with sql in sybase and this one worked fine:

select * from tabletest where datafield like '2004-01-01%'

(date format: yyyy-mm-dd)
ciao

5

You could also consider making a date field directly in your backend database.

6

automazione:
Hi Riccardino,

I tried:

controller.find()

datafield = “#” + utils.dateFormat(globals.datax, “d.M.yyyy”) + ‘|d.M.yyyy’;
controller.search()



where data_field is the data field where to search and datax a global containing the data to search for. Servoy does the sql translations and everything works fine.

Also tried with sql in sybase and this one worked fine:

select * from tabletest where datafield like ‘2004-01-01%’


(date format: yyyy-mm-dd)
ciao

Sorry (never try to write method when you’re tired out) ;-): since I need the result in a html table, the query is launched directly in SQL so, what I had to test was obviously to change “=” with “like”, adding the percent at the end of the date.

Thanks again :-)

7

jaleman:
You could also consider making a date field directly in your backend database.

I thought about it, but what stopped me is the question “Are date only columns supported by all SQL databases?”

An important advantage in using Servoy is its incredible ease to move from a database to another, so I try to avoid to use “custom” instructions in queries, non-standard field etc…

Are Date fields completely standard, as Datetime are?

8

Riccardino:

jaleman:
You could also consider making a date field directly in your backend database.

I thought about it, but what stopped me is the question “Are date only columns supported by all SQL databases?”

An important advantage in using Servoy is its incredible ease to move from a database to another, so I try to avoid to use “custom” instructions in queries, non-standard field etc…

Are Date fields completely standard, as Datetime are?

No they are not, and that is exactly why Servoy by default uses datetime fields.