About date search

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.
songhdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thirdCommented:
AdoQuery1.SQL.Add('select num from house '); //just put a space here
0
snehanshuCommented:
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
kretzschmarCommented:
use a parametertype string
and a code like

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

meikl ;-)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

regards,
khalid.
0
cerdalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geobulCommented:
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
Mohammed NasmanSoftware DeveloperCommented:
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
Mohammed NasmanSoftware DeveloperCommented:
ohh seems too late, I open the page before hour and I just post now before to see the other comments
0
snehanshuCommented:
All,
  I thought AsString, AsDateTime etc. were not available with
AdoQuery.Parameters.ParamByNam...
  mnasman, #39 added quotes around the string value.
...Snehanshu
0
snehanshuCommented:
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
kretzschmarCommented:
>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
geobulCommented:
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
snehanshuCommented:
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
snehanshuCommented:
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
cerdalCommented:
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
Mohammed NasmanSoftware DeveloperCommented:
snehanshu, sorry I thought it was #35, I forgot the ascii codes, no more Dos app ;-)
0
snehanshuCommented:
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
kretzschmarCommented:
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
geobulCommented:
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
cerdalCommented:
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
cerdalCommented:
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
snehanshuCommented:
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
songhdAuthor Commented:
Thanks to  cerdal, snehanshu,geobul, kretzschmar, mnasman,third,Qosai_DBA helping me.
Cerdal answer suit for me.
hehe,I love the web.




0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.