mySQL convert varchar to date

Question

I need to convert a varchar value of 1/9/2011 to a date in mySQL and I want only the month and year. So that I can then use the PERIOD_DIFF function (so I would need the above to be converted to 201101).

I've tried various ways using the STR_TO_DATE function:

SELECT STR_TO_DATE(CYOApp_oilChangedDate, '%m/%Y') FROM CYO_AppInfo

But I get weird results... (for example: 2009-01-00)

What am I doing incorrectly?

Jon Black · Accepted Answer · 2011-01-16 15:49:51Z

79

select date_format(str_to_date('31/12/2010', '%d/%m/%Y'), '%Y%m');

or

select date_format(str_to_date('12/31/2011', '%m/%d/%Y'), '%Y%m');

hard to tell from your example

answered Jan 16, 2011 at 15:49

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harshal Patil Over a year ago

this is perfect solution for string to date

Gilsha · Accepted Answer · 2015-10-06 05:17:46Z

As gratitude to the timely help I got from here - a minor update to above.

$query = "UPDATE `db`.`table` SET `fieldname`= str_to_date( fieldname, '%d/%m/%Y')";

4b0 · Accepted Answer · 2022-11-23 09:56:12Z

select substring_index(your_column_name, '/', 1) years, substring_index(your_column_name, '/', -1) months, substring_index(substring_index(your_column_name, '/', 2),'/', -1) as dates from your_Table_Name;

Then just concat everything and you will have a proper set to work around with.

Collectives™ on Stack Overflow

mySQL convert varchar to date

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related