Solved

About date search

Posted on 2003-12-07
23
525 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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