CODEWITHSUNDEEP
mysqlstr-to-date
user2663104
user2663104

Reputation: 13

str_to_date returns null+mysql

When I am trying to convert varchar to date, I get Null values in return.

I have values as 05-MAR-2015 in my column.

I am running following query.

select STR_TO_DATE('n.Invoice_date','%d-%c-Y') from table n;

Once I run above query I get null values in return.

I want to introduce a new column in date format.

Upvotes: 0

Views: 148

Answers (2)

rbr94
rbr94

Reputation: 2277

Your error is in usage of %c instead of %b to get the date. You mixed the formatting of the date with the creation of a date value. This should do it:

SELECT DATE_FORMAT(STR_TO_DATE(n.invoice_date,'%d-%b-%Y'), '%d-%c-%Y') FROM table n;

This results in: 05-3-2015

Here you first create a date with STR_TO_DATE which must match the format in which it is stored in the field. Then you can format it with DATE_FORMAT in the way you want to.

See the MYSQL Docs for more information: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Upvotes: 0

tadman
tadman

Reputation: 211560

Note that the literal string 'n.invoice_date' is not a valid date. What you mean is:

SELECT STR_TO_DATE(n.invoice_date, '%d-%b-%Y') FROM TABLE n

Upvotes: 1

Related Questions