Solved

About date search

Posted on 2003-12-07
23
524 Views
Last Modified: 2010-04-05
First I use delphi 6.0 to complete my promgram, and the database is sqlserver 2K.
When I search the num who rent the house over the limited date ,it will be chosen.
See the database first: name house
num   char(6)
sta_date datetime
Lim_date datetime
second see my Promgram:(I use adoquery to find out)

procedure TForm5.FormActivate(Sender: TObject);
begin
AdoQuery1.Close;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select num from house');
AdoQuery1.SQL.Add('where lim_date<:ss');
AdoQuery1.Parameters.ParamByName('ss').Value:=Date;
AdoQuery1.Open;
......
end;

When I run it ,it  say wrong about sql-error. I replace the Date with DateToStr(Date),the error always. I use other form of the
date time ,the error always. Can U help me? I hope u run the program  first and answer to me.
0
Comment
Question by:songhd
  • 7
  • 4
  • 3
  • +5
23 Comments
 
LVL 30

Expert Comment

by:third
ID: 9894695
AdoQuery1.SQL.Add('select num from house '); //just put a space here
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9894708
Have you tried

var
 sDate: string;
begin
...
sDate:= #39 + FormatDateTime('mm/dd/yyyy', DATE) + #39;
...
AdoQuery1.Parameters.ParamByName('ss').Value:=sDate;


(i.e. enclosing date value in quotes)
...Snehanshu
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9894895
use a parametertype string
and a code like

AdoQuery1.Parameters.ParamByName('ss').AsString:=FormatDateTime('yyyy/mm/dd', DATE) ;

meikl ;-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:Qosai_DBA
ID: 9894937
Hi songhd,
first third is right.
and do
AdoQuery1.Parameters.ParamByName('ss').DataType:=ftDate;
AdoQuery1.Parameters.ParamByName('ss').AsDateTime:=Date;

regards,
khalid.
0
 
LVL 1

Accepted Solution

by:
cerdal earned 100 total points
ID: 9894956
Use a string parameter rather than a DateTime one:

AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select num from house');
AdoQuery1.SQL.Add('where lim_date<CONVERT(datetime,:ss,110));
AdoQuery1.Parameters.ParamByName('ss').AsString:=FormatDateTime('yyyy-mm-dd',Date);
AdoQuery1.Open;

Chris.
0
 
LVL 17

Expert Comment

by:geobul
ID: 9895000
I would use:

AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select num from house');
AdoQuery1.SQL.Add('where CONVERT(char(8), lim_date, 112) < :ss');
AdoQuery1.Parameters.ParamByName('ss').AsString:=FormatDateTime('yyyymmdd',Date);
AdoQuery1.Open;

Regards, Geo
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 9895158
Hello

   Can you describe your error message please?, are you sure that you assigned connectionstring to theado query?

 I made a quick sample with Orders table that came with Sqlserver sample databases, and I dont have any problems

begin
  AdoQuery1.SQL.Clear;
  AdoQuery1.SQL.Add('select * from orders');
  AdoQuery1.SQL.Add('where orderdate < :d') ;
  AdoQuery1.Parameters.ParamByName('d').Value := date;
// add this line just to debug the sql statement
  ShowMessage(AdoQuery1.SQL.Text);
  AdoQuery1.Open;
end;

BTW, you can the date value to sql server as string, it doesn't require to enclose it with # #

Regards,
Mohammed
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 9895178
ohh seems too late, I open the page before hour and I just post now before to see the other comments
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9895185
All,
  I thought AsString, AsDateTime etc. were not available with
AdoQuery.Parameters.ParamByNam...
  mnasman, #39 added quotes around the string value.
...Snehanshu
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9895221
mnasman,
>> Can you describe your error message please?
Something that should have been asked earlier by all ;-)

Cheers!
...Shu

P.S.
songhd,
  I hope only 'select num from house' works!
:-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9895233
>I thought AsString, AsDateTime etc. were not available with
AdoQuery.Parameters.ParamByNam

could be, not sure yet, but the parameter must be a string,
best results i had with a format yyyy/mm/dd

meikl ;-)
0
 
LVL 17

Expert Comment

by:geobul
ID: 9895264
Yes, there is no AsXXX for ADO parameters. Try this one:

  AdoQuery1.SQL.Clear;
  AdoQuery1.SQL.Add('select num from house');
  AdoQuery1.SQL.Add('where CONVERT(char(8), lim_date, 112) < ');
  AdoQuery1.SQL.Add(QuotedStr(FormatDateTime('yyyymmdd',Date)));
  AdoQuery1.Open;

Regards, Geo
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9895271
And how about not using parameters at all?
Like:

var
 sDate: string;
begin
...

//Include or uninclude #39 depending on what works
//and change 'mm/dd/yyyy' to meikl's yyyy/mm/dd it that helps
  sDate:= #39 + FormatDateTime('mm/dd/yyyy', DATE) + #39;

  AdoQuery1.SQL.Add('select * from orders');
  AdoQuery1.SQL.Add('where orderdate < '+ sDate) ;
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9895309
I just created a temporary table in SQL server 2K and added a few fields.
Then, in the query analyser, I executed the following SQL statements:
1) select * from mytable where MyDate = '2003-01-01'
2) select * from mytable where MyDate = 01/01/2003
3) select * from mytable where MyDate = 2003/01/01
4) select * from mytable where MyDate = '2003/01/01'

(1) and (4) worked, others did not. Implies that the value needs to be embedded in quotes.
So, I assume ADO expects the same.

:-)
...Snehanshu
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9895316
this seems to have got us all going :-)

Here's mine again without parameters:

AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select num from house');
AdoQuery1.SQL.Add('where lim_date<CONVERT(datetime,'''+FormatDateTime('yyyy-mm-dd',Date)+''',110));
AdoQuery1.Open;

Chris.
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 9895322
snehanshu, sorry I thought it was #35, I forgot the ascii codes, no more Dos app ;-)
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9895360
mnasman,
  If all the above doesn't work, I think your coomment was by-far the best:
>>Can you describe your error message please?
:-)
Cheers!
...Snehanshu
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9895369
a did a sample, which works properly

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ADODB, Grids, DBGrids, ComCtrls, DB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DateTimePicker1: TDateTimePicker;
    DBGrid1: TDBGrid;
    procedure DateTimePicker1Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

//sample using german version of northwind.mdb
//select-statement:
//select * from rechnungen where bestelldatum < :ss
//parametertype of :ss is ftString

procedure TForm1.DateTimePicker1Change(Sender: TObject);
begin
  adoquery1.Close;
  adoquery1.Parameters[0].Value := FormatDateTime('yyyy/mm/dd', DateTimePicker1.Date) ;
  adoquery1.Open;
end;

end.

meikl ;-)
0
 
LVL 17

Expert Comment

by:geobul
ID: 9895376
cerdal, CONVERT function can't be used that way. See my comment how to use it.

I strongly recommend using CONVERT function when working with datetime fields in MSSQL because a date like '2003/02/01' or '01/02/2003' is ambiguous (which one is the month?) .

Regards, Geo
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9895459
Geo,

I'm not sure what you mean by 'that way'. I've been using this (barring typo errors) in a function for years with Delphi 3/4/5/6 and MS SQLServer 6.5.

If you mean you can't convert from string to datetime, here's an extract from the SQL Server Books online:

datetime and smalldatetime
When converting to datetime, SQL Server rejects all values it cannot recognize as dates (including dates earlier than January 1, 1753). You can convert datetime values to smalldatetime when the date is in the proper range (January 1, 1900 to June 6, 2079). The time value is rounded up to the nearest minute.

which impliers that you can use it this way.

If I've misunderstood your comment, could you expand on it, please?

Chris.
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9895516
songhd,

> When I run it ,it  say wrong about sql-error.

 mnasman and snehanshu were right to ask what the error is.

Could you also say whether lim_date is a string field or a datetime field?

Chris.
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9895536
All,
  How about waiting till songhd sees all this wonderful discussion and decides which he liked best? I think there are more than enough answers and all have their merits/demirets discussed.
:-)
...Snehanshu
0
 

Author Comment

by:songhd
ID: 9908926
Thanks to  cerdal, snehanshu,geobul, kretzschmar, mnasman,third,Qosai_DBA helping me.
Cerdal answer suit for me.
hehe,I love the web.




0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Simple Delphi Question 9 105
Delphi inherited method 6 78
I want to use librsync in my Delphi backup application. 3 69
Firemonkey BASS_Init into a thread 17 54
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question