SQL - Need help!! 1

[fitzgerald]

Hi all,
Using Delphi 2 (yeah it's out of the ark i know!) and am trying to get some SQL up and running.

I am making a membership system for a video store and I have the main database running which contains all of the members details, along with the date that their video is due for return in DD/MM/YY format.

In one section of the program, I want all of the members that have videos due for return TODAY to be listed in a table. This will involve comparing the date of return stored in the database with todays date.

What would be the SQL needed to perform this?

Thanks for any help that you can offer!

 

[LucieLastic]

What db are you using?

Try this first:

Select * from <MyTable>
where DueDate = GetDate()

 

[LucieLastic]

That was a Sybase eg I gave you. In Oracle, I think SysDate would do it

ie

where DueDate = SysDate

 

[fitzgerald]

Hi Weez,

&quot;What db are you using?&quot;
I think I'm using Paradox 7 db....or am I getting the wrong end of the stick?

I will try both of the tips above when I get home. Many thanks for your time!

 

[cpmasesa]

try:

SELECT * FROM <TableName> WHERE DueDate = Date

or

SELECT * FROM <TableName> WHERE DueDate = Date()


Should work for Paradox and Access.

 

[fitzgerald]

I've tried all the above but I keep getting &quot;Capability Not Supported&quot; messages when I try to run!

Any help you can give is much appreciated

 

[LucieLastic]

You could build the sql yourself, try

qry.sql.add('select * from <TableNAme>');
qry.sql.add('where DueDate = &quot;'+FormatDateTime('DD/MM/YY',now)+'&quot;');
qry.open;

See if you have any joy with that. If not, you may have to change the FormatDateTime format string to eg 'YYYY/MM/DD'.

Let us know how you get on.

lou

 

[VintageWine]

1. If you are building your own SQL, the format (as a string)
YYYYMMDD
works for many databases.

2. Write the SQL as a parameterised query, then let Delphi handle the formatting headache. e.g.

with MyTQuery do begin
Close;
SQL:='SELECT * RentalsOut WHERE DueDate=ueDate';
Params[0]:=Date();
Open;
end;

Good luck

 

[fitzgerald]

Many thanks guys.

Unfortunately I still get the &quot;Capability Not Supported&quot; message, which is starting to make me think that there may be something wrong with the Delphui setup on our network as I see no reason why this isn't working.

Thanks again!

 

[SATWizard]

The problem is your choice of databases. Paradox presents that response to several aspects of &quot;standard&quot; SQL.

Instead of using the SQL statement in a query, try using a table and setting a filter to limit it to displaying/selecting only those records with a DueDate of the current date. (Remember, in D2, Filters are our friends. )

Yeah, I know, that's an iffie concept on a network but, hey, you said you were in D2 which is also an iffie concept. ;-)

 

[yomyom]

Try this first:

Select * from &quot;c:\path\<MyTable>&quot; as a
where a.&quot;DueDate&quot; = :GetDate

1. duedate is a field in my table
2. :getDate is a user defined parameter for the sql query
3. set a grid's datasource to point to query1's datasource

attach this code to a button
query1.close;
query1.unprepare;
query1.params[0].asDate := now;
query1.prepare;
query1.open;

click button to execute sql
good luck
yom yom.