How can I delete records based on a date field in character format

[HuntsvilleMan]

Perhaps someone with more experience can help this novice understand how to write a mySQL query to delete records older than a given day.

 

The table I have been given has has a field named LogDate with dates stored in a 19 character field

format as: yyyy-mm-dd hh:mm:ss

 

What I need to do is delete all records older a given archival day which is also in the character format: yyyy-mm-dd

 

No doubt this would be simpler if the dates were in a normal mySQL date field but alas that's not what life handed me.

 

Thanks for suggestions on how to write this query.

Mike

 

 

 

[fenway]

You need to store those as DATETIME.

 

But mysql will let you get away with that for now.

 

Simply use "WHERE LogDate < NOW() - INTERVAL 1 DAY".

[HuntsvilleMan]

Thanks for helping this novice get pointed in the right direction.  The INTERVAL key word sounds like what I needed.  This will be the third query I've written in mySQL.  No doubt the nuances of mySQL versions are a big part of what separates experts from a newbee like me.  Next time I'll include the mySQL version I'm using - assuming I can find it.  Any other rules I violated?  Really appreciate the suggestion.

[fenway]

There's a sticky that covers all of the rules.