CODEWITHSUNDEEP
mysqlsqlreplacephpmyadmin
user3261715
user3261715

Reputation: 75

update field of table

i have field name birthdate in users database some users birthdate like =

2014/02/18

and some others like =

2014-02-18

i should use birthdate varchar (10)

so i want change 2014/02/18 to 2014-02-18 

SELECT `birthdate` 
FROM `users` 
WHERE `birthdate` LIKE  '%%%%/%%/%%' REPLACE '%%%%-%%-%%'

this code just select users with 2014/02/18

how can I replace that with 2014-02-18 in sql tab?

Upvotes: 1

Views: 39

Answers (3)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

If you just want to replace / with - you can do comparison like this: 

SELECT `birthdate` 
FROM `users` 
WHERE replace( `birthdate`, '/', '-' ) LIKE  '2014-02-18'

Otherwise, remove both / and - from date field values and compare for YYYYMMDD formatted value. 

SELECT `birthdate` 
FROM `users` 
WHERE replace( replace( `birthdate`, '/', '' ), '-', '' ) LIKE  '20140218'

Upvotes: 1

Guy Nethery
Guy Nethery

Reputation: 299

Use replace function

replace(birthdate,'/','-')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You want an update statement if you want to change the data:

update users
    set birthdate = replace(birthdate, '/', '-')
    where birthdate like '%/%/%';

You should, however, learn to store dates as dates and not as strings.

Upvotes: 0

Related Questions