Mysql Wrong Date Format

Question

Hi I am trying to convert dd-mm-yy to yy-mm-dd in mysql but I'm getting wrong date as below :

 query :  select STR_TO_DATE('01-09-2018','%Y-%m-%d')
   result:  2001-09-20

Please help me to solve this issue.

Clearly, the third part is Y. Obviously, we can only guess as to which of the other parts is m — Strawberry, Sep 27 '18 at 07:11

score 2 · Accepted Answer · answered Sep 27 '18 at 07:10

2

Try below: format string is wrong in your case

select STR_TO_DATE('01-09-2018','%d-%m-%Y')

Output:

'2018-09-01'

answered Sep 27 '18 at 07:10

Fahmi

37,315
5
22
31

yes, it's working but may I know how is it possible since you have specified '%d-%m-%Y' format ? – Kiran Kumar Sep 27 '18 at 07:13
1

it is the string format of '01-09-2018' like in this string it is d then m then Y so in format you've to specify in that order otherwise it will not work – Fahmi Sep 27 '18 at 07:14
1

yes I wll mark it Accepted, Thank You .. – Kiran Kumar Sep 27 '18 at 07:17
1

@KiranKumar I've added an explanation in my answer which explains why you were mistaken. – Blue Sep 27 '18 at 07:21

Madhur Bhaiya · Answer 2 · 2018-09-27T07:19:30.850

You need to use STR_TO_DATE function in the right way. It takes a string str and a format string format. Format pattern has to match your input str. In your case, you had dd-mm-yy has input, so you have to use matching format, that is, %d-%m-%Y

Try the following:

select STR_TO_DATE('01-09-2018','%d-%m-%Y')

Additional Details:

%d Day of the month as a numeric value (01 to 31)
%m Month name as a numeric value (00 to 12)
%Y Year as a numeric, 4-digit value
Note that STR_TO_DATE function will always return in MySQL standard date format, which is yyyy-mm-dd (if input is date only).
If you want to convert into some other format, then you will need to use a combination of STR_TO_DATE alongwith DATE_FORMAT function.

score 1 · Answer 3 · answered Sep 27 '18 at 07:19

Simply put, you're confusing the parameters. In your example you provide %Y-%m-%d which is your expected output. The second parameter of STR_TO_DATE should be the format of the string you're supplying:

SELECT STR_TO_DATE('01-09-2018','%d-%m-%Y')

The default MySQL datestring is: %Y-%m-%d, which is what is yielded from the query above:

2018-09-01

See the following SQL fiddle: https://www.db-fiddle.com/f/2411rcvjH6dKZYLzxkii39/0

score 0 · Answer 4 · answered Sep 27 '18 at 07:11

0

It seems you wrote the parsing parameters wrong... the should point to the string's parameters

SELECT
    STR_TO_DATE('01-09-2018','%d-%m-%Y');

answered Sep 27 '18 at 07:11

Guy Louzon

1,175
9
19

Mysql Wrong Date Format

4 Answers4