CODEWITHSUNDEEP
mysqlsql
Amin SCO
Amin SCO

Reputation: 1972

truncate inccorrect datetime value 30.05.2012

i have problem with my query for date. i have date format for field value 30.05.2012. i want to change this format to 30/05/2012 but my query doesn't work. can i know what wrong with my query?

This is my query:-

select DATE_FORMAT(P2, '%d/%m/%Y') from view_parameter a
where a.stateCode = 02 and a.schoolCode='SMSA'

Upvotes: 0

Views: 85

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

You forgot a % before your d

select DATE_FORMAT(P2, '%d/%m/%Y') from view_parameter a where a.stateCode = 02 and a.schoolCode='SMSA'

Edit : Oh, that's a string that mysql can't get as a Date : so

select DATE_FORMAT(STR_TO_DATE(P2, '%d.%m.%Y'), '%d/%m/%Y') ...

Edit 2 :

or simply (without check for valid dates which is made by STR_TO_DATE)

select replace(P2, '.', '/')

Upvotes: 2

Related Questions