Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

Difference of Query results using 'Between ...and ...' in SQL Sercer 2000 and Delphi6.0 professional

Hi, all

I want to query the data in a limited period e.g. from 3/2/2003 to 3/21/2003.

I get the desired result in sql sqrver 2000 using the statements as follows

Select *
from tablename
where datacolumn between '3/2/2003' and '3/21/2003'

The query result include the data with the datacolumn in 3/2/2003 and 3/21/2003.

But unfortunately, when I execute the above statement in Delphi6.0 by TQuery like the following

frmData.tsqlInvoice.close;
frmData.tsqlInvoice.unprepare;
frmData.tsqlInvoice.clear;            
frmdata.tsqlInvoice.SQL.add('Select * from tablename');
frmdata.tsqlInvoice.SQL.add('where datacolumn between :begin and :end');
frmdata.tsqlInvoice.Params[0].AsDateTime  := dtpBegin.DateTime ;
frmdata.tsqlInvoice.Params[1].AsDateTime := dtpEnd.datetime;
frmData.tsqlInvoice.Prepare ;
frmData.tsqlInvoice.open;

The result excludes the data with the datacolumn in 3/2/2003. So, in order to get the data with the datacolumn in 3/2/2003 I have to set the begin date as 3/1/2003.

Can you tell me what the problem is and how to get the desired query result in Delphi6.0 professional?

Great thanks

Jason
0
jasoncen
Asked:
jasoncen
1 Solution
 
kretzschmarCommented:
there may a timefragment
try following change

frmdata.tsqlInvoice.Params[0].AsDateTime  := trunc(dtpBegin.DateTime);
frmdata.tsqlInvoice.Params[1].AsDateTime := trunc(dtpEnd.datetime);

meikl ;-)
0
 
ILECommented:
my hint


there may a timefragment
try following change

frmdata.tsqlInvoice.Params[0].AsDateTime  := trunc(dtpBegin.DateTime);
frmdata.tsqlInvoice.Params[1].AsDateTime := trunc(dtpEnd.datetime+1);

if the target colmun is datetime type if not all must be worknig;

ile :))
0
 
kretzschmarCommented:
usual most databases storing dates as datetime, ile

;-)
0
 
hetturkiCommented:
Hi
First try it in this way & dont use use the keyworks such as Begin , End as parameters.

frmData.tsqlInvoice.close;
frmData.tsqlInvoice.unprepare;
frmData.tsqlInvoice.clear;            
frmdata.tsqlInvoice.SQL.add('Select * from tablename');
frmdata.tsqlInvoice.SQL.add('where datacolumn between :begin and :end');
frmdata.tsqlInvoice.Parameters.ParaByName('begin').value  := dtpBegin.DateTime ;
frmdata.tsqlInvoice.Parameters.ParaByName('end').value := dtpEnd.datetime;
frmData.tsqlInvoice.open;
0
 
jasoncenAuthor Commented:
Hi, kretzschmar

Based on your advice I have successfully solve my problems.

Great thanks!

Jason
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now