2

I am trying to insert VARCHARs strings which are not a valid date format into another table where they are also stored as a VARCHAR. I'm trying to use STR_TO_DATE to accomplish this, which is supposed to return NULL if a string's format is invalid:

    INSERT INTO DateErrors
    SELECT `row_id`,`date_string`
    FROM `source_table`
    WHERE STR_TO_DATE(`date_string`,'%d-%m-%Y') IS NULL

Which throws an error for strings with an invalid date format: Error Code: 1411. Incorrect datetime value: 'some random string that isnt a date' for function str_to_date. Instead of throwing this error, I want these string values inserted into DateErrors.

However, running just the SELECT query returns the rows where date_string is invalid without throwing an error.

Ravi
  • 30,829
  • 42
  • 119
  • 173
Steven
  • 153
  • 1
  • 7
  • Why not just `WHERE date_string IS NULL`? No need to format it if you're looking for null values. – PaulProgrammer Oct 19 '17 at 17:16
  • @PaulProgrammer I think that's under the assumption that invalid dates return `NULL`, but they may in fact show up as `00-00-0000`. – tadman Oct 19 '17 at 17:20
  • You really should be steering towards using `DATE` columns and *only* `DATE` columns for storing dates to avoid this problem in the first place. The ideal form is `YYYY-MM-DD`, or ISO-8601 format. All others lead to ambiguity. – tadman Oct 19 '17 at 17:21
  • @tadman The `source_table` contains raw, unvalidated strings that might not be in a correct date format. I'm trying to identify the fields that are invalid. – Steven Oct 19 '17 at 17:30
  • @Steven I get that. I'm just saying you should work to get rid of invalid dates, clean up your data, and then prevent additional garbage from showing up by switching to the proper column type. There's no point in institutionalizing this mistake. – tadman Oct 19 '17 at 17:33

1 Answers1

2

You are specifying wrong format for your date string in STR_TO_DATE

STR_TO_DATE(`date_string`,'%d-%m-%Y')

instead, it should be 

STR_TO_DATE(`date_string`,'%Y-%m-%d')
Ravi
  • 30,829
  • 42
  • 119
  • 173
  • I want to insert rows from `source_table` into `DateErrors` where `date_string` doesn't match the format '%d-%m-%Y' – Steven Oct 19 '17 at 17:35
  • @Steven I'm not sure, what are you asking, but I gave you solution for your issue. You need to show some data to understand your issue – Ravi Oct 19 '17 at 17:37