Selecting records with a condition

[ashleysmithd]

Hi.

Slightly amateur question here (I'm new to SQL)

In Delphi, in using the ODBC, I'm trying to bring up a list of records that contain a 'date + time' attribute, that are smaller than the current date.

And is there anyway to bring up separate attributes such as 'first name' and 'surname' that correspond to the record being shown?

Sorry for such a basic question, I've just started learning SQL, and need it to complete my coursework.

Any help would be appreciated, thanks.

[Jordan]

Hello,
Without knowing the table structure and the date/time format (is it Unix Timestamp?) it may look something like this:

SELECT tbl.firstname, tbl.surname
FROM tbl AS tbl
WHERE date < SYSDATE;

If you give me the table structure and more info (like what SQL client you are using - MySQL, Access, msSQL, etc.) I can help you further.

[ashleysmithd]

Originally Posted by Jordan

Hello,
Without knowing the table structure and the date/time format (is it Unix Timestamp?) it may look something like this:

SELECT tbl.firstname, tbl.surname
FROM tbl AS tbl
WHERE date < SYSDATE;

If you give me the table structure and more info (like what SQL client you are using - MySQL, Access, msSQL, etc.) I can help you further.

Hi Jordan, thanks for the reply.

I'm using the ODBC feature in Delphi 5. I'm building the database into my software.

I built the database using Access, but exported it into a .DB format, a paradox database format if I can remember correctly.

At the moment I can successfully execute queries and return data. However, only to a degree.

This is basically what I want to do:

SELECT TimeDateIn, FirstName, Surname
FROM orders.DB
WHERE TimeDateIn < SYSDATE

I've managed to get the multiple fields working, because of the code you posted, thankyou for that. But I'm having some difficulty getting the 'SYSDATE' to work. It just comes up with 'Invalid Field Name', as if it doesn't recognise the line atall.

Once again, thankyou for your time.

[ashleysmithd]

Anyone?

Sorry for bumping the thread but I need to hand this project in in a few weeks time and I'm getting really desperate about it now. All I need to do is get some SQL querys working.

I think I've figured out what date function Delphi 5 understands, CURRENT_DATE(). However it says that the compatability is not supported.

Thanks!

[ashleysmithd]

A quick update,

I spoke to a fellow student today, and he has told me that it won't work by comparing the database attribute with an SQL function such as current_date().

Apparently, I have to set a variable inside the 'params'. And compare the variable with the database attribute.

The only problem is... The button to add a new param is greyed out. And I can't see anything obvious to change that.

Thanks.

[NeedHelp]

So basically the SYSDATE cannot be used in a "Where"? Couldn't you do that in the SQL too?

SELECT variable, SYSDATE AS myDate
..........
WHERE TimeDateIn < myDate;

[WillB]

Originally Posted by NeedHelp

So basically the SYSDATE cannot be used in a "Where"? Couldn't you do that in the SQL too?

SELECT variable, SYSDATE AS myDate
..........
WHERE TimeDateIn < myDate;

I agree, it should work in the SQL

If you look around some of the documentation on ODBC it may have another method listed...

[ashleysmithd]

Unfortunately nothing came up, however it doesn't matter as I'm just going to write it in, the examiner isn't going to have a copy of the software itself. So he/she won't know it doesn't work.

Anyway, thanks the replies guys it is appreciated.