How can I fix this error while trying to update a date in a sql db?

Question

I have a website where a user clicks a submit button and then a record in my database is updated. I'm trying to make it so that the time and date of when they clicked submit is saved to the database as well however I'm having problems with my query and the database isn't updating. My query is:

$query = (("
    UPDATE markers 
    SET questTitle ='$questTitle', 
        category='$category', 
        questReward ='$questReward', 
        date_submitted =DATE(STR_TO_DATE($date_submitted, '%m/%d/%Y')) 
    WHERE id = '$id'
"));

I'm getting an error that reads:

failedInvalid query: Incorrect datetime value: '2018-10-26' for function str_to_date

Any help would be appreciated, thank you.

Learn to use parameters to, well, pass parameters into query strings. Don't munge the string. — Gordon Linoff, Oct 26 '18 at 15:25
The date string you're passing does not match the format you're passing. — aynber, Oct 26 '18 at 15:29
`date_submitted` is `%m/%d/%Y`? If so why? Use a `DATE` column. — user3783243, Oct 26 '18 at 15:31
If you're going to be passing in different formats of dates, check the formats and modify them before pushing them into the database. It will eliminate some of the headache. — aynber, Oct 26 '18 at 15:43
I really hope you're [sanitizing your user input](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) before building that query. Also, not sure what the `(( ))` is about... — Mikkel, Oct 26 '18 at 16:14

maio290 · Answer 1 · 2018-10-26T15:35:18.960

1

failedInvalid query: Incorrect datetime value: '2018-10-26' for function str_to_date

And you're using the format %m/%d/%Y in STR_TO_DATE where it has to be:

%Y-%m-%d

See: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

But see @aynber's comment, the format %Y-%m-%d is already mysql's date format.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

-> https://dev.mysql.com/doc/refman/8.0/en/datetime.html

So there is no need to actually parse it again.

edited Oct 26 '18 at 15:35

answered Oct 26 '18 at 15:30

maio290

6,440
1
21
38

2

Additionally, none of the date functions are needed because `2018-10-26` is a valid mysql date string. – aynber Oct 26 '18 at 15:31
Added it into the answer, thanks. How could I miss that :S – maio290 Oct 26 '18 at 15:35

score 0 · Accepted Answer · answered Oct 26 '18 at 15:37

maio290 answered this question correctly as asked, but I wanted to point out that if you simplify this to:

date_submitted = CURDATE()

Then you can eliminate the need to submit the current date from your application at all, and just let your sql server figure it out. This can also prevent people from trying to tamper with timestamps depending on where you are getting $date_submitted from.

How can I fix this error while trying to update a date in a sql db?

2 Answers2