Join 3,513 readers in helping fund MetaFilter (Hide)


My MySQL Kung Fu is not strong...
June 24, 2014 9:12 AM   Subscribe

I need a script for a MySQL table. The table has multiple fields but one ENDDATE will be populated as a Date/time value. When I am 90 days from that date, I need to update the field STATUS with the value of "email" Work your magic hive! thanks
posted by keep it tight to Computers & Internet (9 answers total)
 
update table [mytable] set STATUS = "email" where ENDDATE (less than or equal to) TODAY + 90

Will that work? Or do you mean 90 days PAST that date?

(sorry about the code fail for the HTML for less than)
posted by jillithd at 9:17 AM on June 24


The date could be any day...say it's 11/1/2014...I need the insert 90 days prior to 11/1
posted by keep it tight at 9:20 AM on June 24


are you planning to run a nightly script to do an update to the field? with a script such as jillithd suggested? or do you need it to do more automatically?
You might look into
http://dev.mysql.com/doc/refman/5.6/en/events.html if you dont have a procedure for running external scripts nightly.
posted by TheAdamist at 9:24 AM on June 24


OK. Then my script will work as long as you automate it like TheAdamist says. The key is that "TODAY" is a reserved word in SQL that knows what the value of today is. Similar to the today() function in Excel.
posted by jillithd at 9:27 AM on June 24


So I have UPDATE dwdata.contracts SET PROCESSING_STATUS = "Email" WHERE END_DATE < "TODAY+90"

I am getting the error " incorrect datetime value 'TODAY + 90' for column 'END_DATE' at row 1
posted by keep it tight at 9:45 AM on June 24


Just poppin in to say it looks like it's reading "TODAY + 90" as a string which it then can't use when comparing to a date.
posted by Brainy at 9:47 AM on June 24


I don't believe MySQL recognises TODAY, I'm not even sure it is SQL. The other problem is that datetime comparisons can be somewhat tricky as results can vary depending on when you run the query. Best to coerce to date values explicitly using something like:

update tablename set STATUS = "email" where date(date_sub(ENDDATE, INTERVAL 90 day)) <= date(now())
posted by epo at 9:58 AM on June 24


Thanks epo...that did it!
posted by keep it tight at 10:16 AM on June 24


Sorry about that. "Today" is definitely recognized by the ESQL queries I run on an Informix database.

I guess I am not familiar enough with the differences between MySQL and Informix SQL.
posted by jillithd at 11:41 AM on June 24


« Older Do you have experience with co...   |  Asking on behalf of a friend (... Newer »

You are not logged in, either login or create an account to post comments