Need Date Comparison and delete record query

[alan93]

I have a date field in records that is formatted as such:

 

1/1/2009

 

or

 

4/5/2007

 

This field that holds these values is a varchar(16)

 

I need to delete all records that has a date older than todays date.

How do I do that?

[ainoy31]

I would change the field to a 'date' data type rather than varchar(16).  Try this code out:

 

select * from table_name WHERE date_column <= CURDATE()

or

select * from table WHERE date_column >= CURDATE() 

 

In your case you would use Delete but try the Select first to make sure that it returns the records you need.

[discomatt]

I have a date field in records that is formatted as such:

 

1/1/2009

 

or

 

4/5/2007

 

This field that holds these values is a varchar(16)

 

I need to delete all records that has a date older than todays date.

How do I do that?

 

Good luck doing it with VARCHAR. Shoudla done with a date type column.

[Maq]

If I were you I would create a new field with date_time datatype and write a script to go through your current varchar dates and convert them...  It will make things easier for this situation and future problems.

[PFMaBiSmAd]

After you add a DATE type column, a single UPDATE query using the mysql STR_TO_DATE() function can populate the new column from the existing data.

[alan93]

I didn't know you could change the type after the column was populated. I guess I could try that.

Hope it doesn't break anything.

 

[

quote author=ainoy31 link=topic=220469.msg1011351#msg1011351 date=1223666580]

I would change the field to a 'date' data type rather than varchar(16).  Try this code out:

 

select * from table_name WHERE date_column <= CURDATE()

or

select * from table WHERE date_column >= CURDATE() 

 

In your case you would use Delete but try the Select first to make sure that it returns the records you need.

[Maq]

I think he meant create a new column with the date type use the code to populate it, delete ur old column and rename the new one.