1

I want to update data in mysql table on basis of date field value.

Table consist of four fields (id, file_id, date_of_purchase, error). date_of_purchase and error are varchar fields.

Now i want to update error field if value in date is not a valid date. I tried with STR_TO_DATE() function but it is not working in where clause in update query

Here is my query

UPDATE `temp_bulk_upload_data` SET `error`= 'Date of purchase: Date of purchase should be a valid date' WHERE `file_id`='190' AND STR_TO_DATE(`date_of_purchase`, '%m/%d/%Y') IS NULL AND (`date_of_purchase` != '' OR `date_of_purchase` IS NOT NULL)

In this query i am getting this error:

Error in query (1411): Incorrect datetime value: '27/8/2018' for function str_to_date
Ninja Turtle
  • 1,293
  • 2
  • 24
  • 50
  • 4
    You should not be using VARCHAR fields to store date values to begin with. – misorude Aug 28 '18 at 09:51
  • @misorude i know but it is already there in data. – Ninja Turtle Aug 28 '18 at 09:54
  • Possible duplicate of : https://stackoverflow.com/questions/2690205/mysql-how-to-check-if-a-string-is-a-valid-date-time-or-datetime – Mickaël Leger Aug 28 '18 at 09:59
  • 1
    "_but it is already there in data_" You could change it – brombeer Aug 28 '18 at 10:06
  • @MickaelLeger I checked that. that is different thing i don't want to use for loops. i want to do it in single query. – Ninja Turtle Aug 28 '18 at 10:12
  • Are you sure it's not working? The behaviour you're getting seems to be exactly as described in the manual - it returns NULL and issues a warning (not note an error, a warning - AFAIK it won't stop the query from executing, but correct me if I'm wrong) – ADyson Aug 28 '18 at 10:57
  • BTW `AND (date_of_purchase != '' OR date_of_purchase IS NOT NULL)` doesn't make much sense - this will allow NULL values to be used, is that what you intended? It's not clear what this clause is meant to be doing. If the value is not `''` then either it's NULL or it's populated with something else. If it's populated with something else, then `IS NOT NULL` will allow for that already (and that will also allow `''`, since that's also a non-null value), whereas if it's NULL then it will be allowed through anyway. As far as I can see this clause doesn't prevent any rows from being included. – ADyson Aug 28 '18 at 10:59
  • Hi, I think I found an exact dupe for your [newly deleted question](https://stackoverflow.com/q/52721447/3832970), see https://stackoverflow.com/questions/6710236/function-to-create-regex-matching-a-number-range – Wiktor Stribiżew Oct 09 '18 at 13:09

2 Answers2

1

Use Regular expression in this case. Query 

WHERE `date_of_purchase` NOT REGEXP '^(0[1-9]|1[0-2])\\/(0[1-9]|[1-2][0-9]|3[0-1])\\/[0-9]{4}$'
Ninja Turtle
  • 1,293
  • 2
  • 24
  • 50
Mahesh Kathiriya
  • 612
  • 7
  • 9
0

Value passed in STR_TO_DATE should be of mmddYYYY format Or update second param of STR_TO_DATE function to accept ddmmYYYY format date value.

UPDATE `temp_bulk_upload_data` SET `error`= 'Date of purchase: Date of purchase should be a valid date' WHERE `file_id`='190' AND STR_TO_DATE(`date_of_purchase`, '%d/%m/%Y') IS NULL AND (`date_of_purchase` != '' OR `date_of_purchase` IS NOT NULL)
Manish Chauhan
  • 595
  • 2
  • 7
  • 14