Board index » delphi » SQL Query and DateTime Field accuracy problem.

SQL Query and DateTime Field accuracy problem.

I have what I think is a date time accuracy problem.  I use MSSQL 6.5,
NT 4.0 and Delphi 3.01.  I have a table with a datetime field as one
of the key fields.  I need to find a record using the datetime field.
This date is filled in using the now statement and an example is
35782.349833 (12/18/97 8:23:45.5712 AM).  I think my problem is when I
do

         QRY.ParamsByName('vdate').asdatetime = vdatefld

the actual value used is '12/18/97 8:23:45 AM' which is off by
milliseconds.

How can I get the correct value in the query?

Thanks.

Don Gollahon (dlgl...@ibm.net)

 

Re:SQL Query and DateTime Field accuracy problem.


Quote

> How can I get the correct value in the query?

If milliseconds are the problem then you could pass NOW into a
formatDateTime function that gets rid of the milliseconds, seconds or
whatever you want it to. Then pass that string into a StrToDateTime call
which will give you a TDateTime w/o the milliseconds.

You can do that to NOT put milliseconds into the DB.

But to answer the question ; If milliseconds are blowing up the query then
try a test query with some >= and <= logic in it to see if you can at least
zero in on the record you want. If you can then I guess you'll have to
employ that same logic in the query.

Re:SQL Query and DateTime Field accuracy problem.


I need the time exactly as it is.  I'll just have to fudge in the
milliseconds myself because if I query using '12/18/97 8:23:45:572 AM'
I get the record.

Thanks.

Quote
"Curt Tabor" <cta...@ixpres.com> wrote:

>> How can I get the correct value in the query?

>If milliseconds are the problem then you could pass NOW into a
>formatDateTime function that gets rid of the milliseconds, seconds or
>whatever you want it to. Then pass that string into a StrToDateTime call
>which will give you a TDateTime w/o the milliseconds.

>You can do that to NOT put milliseconds into the DB.

>But to answer the question ; If milliseconds are blowing up the query then
>try a test query with some >= and <= logic in it to see if you can at least
>zero in on the record you want. If you can then I guess you'll have to
>employ that same logic in the query.

Don Gollahon (dlgl...@ibm.net)

Re:SQL Query and DateTime Field accuracy problem.


Don,

If possible convert your key field to a string type and store the datetime
as a string.
You can format it to your desired accuracy using the FormatDateTime
function which will allow you to specify how the date time string will be
formatted.

A question I have is why the milliseconds are being truncated in the first
place.  The "AsDateTime" property should not be causing a problem.  Try
instantiating your query parameter as using the "AsFloat" property
(TDateTime is a double) which will cause the param field to be loaded as a
double.

Regards,
Mark

Don Gollahon <dlgl...@ibm.net> wrote in article
<3499465b.8489...@forums.borland.com>...

Quote
> I have what I think is a date time accuracy problem.  I use MSSQL 6.5,
> NT 4.0 and Delphi 3.01.  I have a table with a datetime field as one
> of the key fields.  I need to find a record using the datetime field.
> This date is filled in using the now statement and an example is
> 35782.349833 (12/18/97 8:23:45.5712 AM).  I think my problem is when I
> do

>     QRY.ParamsByName('vdate').asdatetime = vdatefld

> the actual value used is '12/18/97 8:23:45 AM' which is off by
> milliseconds.

> How can I get the correct value in the query?

> Thanks.

> Don Gollahon (dlgl...@ibm.net)

Re:SQL Query and DateTime Field accuracy problem.


I got the form to work partially.  I used the asFloat method and it
still doesn't find it because of the accuracy problems so I created a
loop to add 0.00000001 to the float and rerun the query (up to 4
times) until it finds it.  Then it works.  I stepped through it and
found the first time it runs the query it uses 370 milliseconds, the
second time it uses 373 milliseconds and a match is found.  (The
actual value in the table is 35782.3499117245374, the value in the
query parameter that finds the record is 35782.3499117345374.)

My problems now is, I allow the user to alter the data for that record
(except for the key fields) and then save it.  I'm using an updatesql
component for this.  And the update doesn't work because it uses the
original key field values for the update which don't create a match.

"Mark G. Gillen" <mggil...@gte.net> wrote:

Quote
>Don,

>If possible convert your key field to a string type and store the datetime
>as a string.
>You can format it to your desired accuracy using the FormatDateTime
>function which will allow you to specify how the date time string will be
>formatted.

>A question I have is why the milliseconds are being truncated in the first
>place.  The "AsDateTime" property should not be causing a problem.  Try
>instantiating your query parameter as using the "AsFloat" property
>(TDateTime is a double) which will cause the param field to be loaded as a
>double.

>Regards,
>Mark

>Don Gollahon <dlgl...@ibm.net> wrote in article
><3499465b.8489...@forums.borland.com>...
>> I have what I think is a date time accuracy problem.  I use MSSQL 6.5,
>> NT 4.0 and Delphi 3.01.  I have a table with a datetime field as one
>> of the key fields.  I need to find a record using the datetime field.
>> This date is filled in using the now statement and an example is
>> 35782.349833 (12/18/97 8:23:45.5712 AM).  I think my problem is when I
>> do

>>         QRY.ParamsByName('vdate').asdatetime = vdatefld

>> the actual value used is '12/18/97 8:23:45 AM' which is off by
>> milliseconds.

>> How can I get the correct value in the query?

>> Thanks.

>> Don Gollahon (dlgl...@ibm.net)

Don Gollahon (dlgl...@ibm.net)

Other Threads