Incorrect parameter count in the call to native function 'str_to_date'

Question

There is a table tab1:

|creation_date      | acc_num             | status|
|-------------------|---------------------|-------|
|31.03.2021 07:43:43| 11111111111111111111| deny  |
|31.03.2021 07:43:43| 11111111111111111111| deny  |
|31.03.2021 01:43:20| 22222222222222222222| admit |
|30.03.2020 21:13:21| 33333333333333333333| deny  |
|30.03.2021 20:28:19| 22222222222222222222| deny  |
|30.03.2021 20:28:19| 44444444444444444444| deny  |

when I used str_to_date(), I had this uncorrect result and errors.

Query:

    select year(str_to_date(creation_date, "%d.%m.%Y %h:%i:%s")) as year
    from tab1;

logs:

    Incorrect parameter count in the call to native function 'str_to_date'
    Unknown column 'creation_date' in 'field list'
    Incorrect datetime value: '30.03.2020 21:13:21' for function str_to_date
    ...
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    from tab1' at line 1
    Incorrect datetime value: '30.03.2020 21:13:21' for function str_to_date
    ...
    Incorrect datetime value: '30.03.2021 20:28:19' for function str_to_date

result:
|year|
|----|
|2021|
|2021|
|2021|
|[NULL]|
|[NULL]|
|[NULL]|

The question is How can I get this result:

|year|
|----|
|2021|
|2021|
|2021|
|2020|
|2021|
|2021|

MySQL version is 8.0.23

score 1 · Answer 1 · answered Apr 06 '21 at 18:21

1

Seems like this should work -- check out the fiddle.

select year(STR_TO_DATE(c,'%d.%m.%Y'))
from tab;

http://sqlfiddle.com/#!9/8cfa2a5/3

answered Apr 06 '21 at 18:21

Aron

756
8
14

Thanks, it works, but returns errors in logs – Vadim.M. Apr 06 '21 at 18:45

score 1 · Answer 2 · answered Apr 06 '21 at 18:21

1

You have wrong hour specifier, should be %H in order to parse 24-hour format time.

answered Apr 06 '21 at 18:21

Eugene

1,357
1
15
17

Caught it! That was the problem. Thanks! – Vadim.M. Apr 06 '21 at 18:46
@Vadim.M., did you change the accepted answer for a particular reason? Remember, you can mark only a one answer as accepted. – Eugene Apr 19 '21 at 20:58
Yes, sorry. I forgot about it. – Vadim.M. Apr 26 '21 at 08:58
@Vadim.M., no problem, do you mind accepting it back? – Eugene Apr 26 '21 at 09:38

score 1 · Accepted Answer · answered Apr 06 '21 at 18:45

1

If you just want the year, why not use a substring operation? After all, you're storing the value as a string:

select substr(creation_date, 7, 4) as year

In the meantime, you should fix your table so it stores date/time values using the correct data type.

answered Apr 06 '21 at 18:45

Gordon Linoff

1,122,135
50
484
624

Incorrect parameter count in the call to native function 'str_to_date'

3 Answers3