Problems with DATES in select statement

Hello,

I am currently trying to write a script that generates a table of selected rows when the date (in the full table) is more than 30 days old, ie from curdate().

This is the basic layout of my table

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
(
pkey INTEGER(10) NOT NULL,
transacno INTEGER(10) UNSIGNED NOT NULL REFERENCES accounts(accountno),
sum INTEGER(10) NOT NULL,
date DATE NOT NULL DEFAULT "0000-00-00",
PRIMARY KEY (pkey)
);



Help is greatly appreciated, i am a beginner to MySql and am busting my nut on this one...

Thanks
Chris

I believe your default date is not a valid inserttion of a date value. I would recommend to use an actual year and number. For instance, 01-01-1710

Then in the select statement incorporate that all values after this particular date are valid.. Or something like that. But perhaps it would be good to post in here what you are trying to accomplish.

I have inserted values into this table already using INSERT INTO transaction VALUES(etc) so hopefully the default value will never have to be called....
Im not sure i can explain with any more detail what i am trying to do.. all i can say is that i want any sample data (in this case transactions that have been made 30 days before the current date) to be extracted from the full table (the rows) and be used to make a separate table, ie where all the dates of transactions are more than 30 days old....

i hope this clears things up

regardless of if the default date is used. It still has to be properly formatted. But why do you wish to create a new table in de database?

If you wish to display the values from the database a simple

select *
from designated table
where DateDiff("d", [kolumn that holds the date], Now() > 30

Or perhaps the syntax is little different.

Then all information is in the query and thus you can do something with it. (For instance, change values in memmory e.a. the information in the database is not altered by that.