# TIME data in DATETIME fields problem

**URL:** <https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422>\
**Category:** Classic Servoy\
**Created:** [August 6, 2004, 6:34pm UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422 "2004-08-06T18:34:32Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Riccardino](https://avatars.discourse-cdn.com/v4/letter/r/41988e/32.png) [@Riccardino](https://forum.servoy.com/u/Riccardino)\
**Post date:** [August 6, 2004, 6:34pm UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/1 "2004-08-06T18:34:32Z")

</div>

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:]( "Wink")

---

<div class="post-metadata">

**Author:** ![automazione](https://avatars.discourse-cdn.com/v4/letter/a/8797f3/32.png) [@automazione](https://forum.servoy.com/u/automazione)\
**Post date:** [August 6, 2004, 10:15pm UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/2 "2004-08-06T22:15:51Z")

</div>

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=1732)  
[http://forum.servoy.com/viewtopic.php?t=1775](http://forum.servoy.com/viewtopic.php?t=1775)

ciao

---

<div class="post-metadata">

**Author:** ![Riccardino](https://avatars.discourse-cdn.com/v4/letter/r/41988e/32.png) [@Riccardino](https://forum.servoy.com/u/Riccardino)\
**Post date:** [August 8, 2004, 8:04pm UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/3 "2004-08-08T20:04:29Z")

</div>

> 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”

---

<div class="post-metadata">

**Author:** ![automazione](https://avatars.discourse-cdn.com/v4/letter/a/8797f3/32.png) [@automazione](https://forum.servoy.com/u/automazione)\
**Post date:** [August 8, 2004, 9:59pm UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/4 "2004-08-08T21:59:13Z")

</div>

Hi Riccardino,

I tried:

```auto
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:

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

```

(date format: yyyy-mm-dd)  
ciao

---

<div class="post-metadata">

**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\
**Post date:** [August 9, 2004, 8:01am UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/5 "2004-08-09T08:01:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Riccardino](https://avatars.discourse-cdn.com/v4/letter/r/41988e/32.png) [@Riccardino](https://forum.servoy.com/u/Riccardino)\
**Post date:** [August 9, 2004, 8:16am UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/6 "2004-08-09T08:16:34Z")

</div>

> automazione:  
> Hi Riccardino,
> 
> I tried:
> 
> ```auto
> controller.find()
> 
> ```

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

> ```auto
> 
> 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%’
> 
> ```auto
> 
> (date format: yyyy-mm-dd)
> ciao
> 
> ```

Sorry (never try to write method when you’re tired out) ![;-)]( "Wink"): 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 ![:-)]( "Smile")

---

<div class="post-metadata">

**Author:** ![Riccardino](https://avatars.discourse-cdn.com/v4/letter/r/41988e/32.png) [@Riccardino](https://forum.servoy.com/u/Riccardino)\
**Post date:** [August 9, 2004, 8:20am UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/7 "2004-08-09T08:20:38Z")

</div>

> 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?

---

<div class="post-metadata">

**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\
**Post date:** [August 9, 2004, 8:44am UTC](https://forum.servoy.com/t/time-data-in-datetime-fields-problem/2422/8 "2004-08-09T08:44:06Z")

</div>

> 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.