Solved

About date search

Posted on 2003-12-07
23
519 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
Comment Utility
AdoQuery1.SQL.Add('select num from house '); //just put a space here
0
 
LVL 5

Expert Comment

by:snehanshu
Comment Utility
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
Comment Utility
use a parametertype string
and a code like

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

meikl ;-)
0
 
LVL 1

Expert Comment

by:Qosai_DBA
Comment Utility
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
Comment Utility
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
Comment Utility
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:mnasman
Comment Utility
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:mnasman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:geobul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:mnasman
Comment Utility
snehanshu, sorry I thought it was #35, I forgot the ascii codes, no more Dos app ;-)
0
 
LVL 5

Expert Comment

by:snehanshu
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now