CODEWITHSUNDEEP
phpmysqldate
Er.KT
Er.KT

Reputation: 2860

compare date stored in varchar in mysql

I am getting one issue, I am trying to fetch all records whose date is less than current date, but problem is date stored in 15-Nov-13 formate(in varchar(20))

now how to compare it?

I tried

SELECT date_format( str_to_date( `ENDdatetime` , '%d/%M/%y' ) , '%Y%m' ) , `CLIENT_PROMO_ID`
FROM `tb_um_promo_schedule`
ORDER BY `CLIENT_PROMO_ID` DESC
LIMIT 0 , 30

but it returns NULL,

so what to do here?

Upvotes: 0

Views: 731

Answers (1)

Fluffeh
Fluffeh

Reputation: 33522

The format you gave in your question (15-Nov-13) does not match the format you are trying to convert in this:

'%d/%M/%y'

Should it not be

'%d-%M-%y'

instead?

SELECT date_format( str_to_date( `ENDdatetime` , '%d-%M-%y' ) , '%Y%m' ) , `CLIENT_PROMO_ID`
FROM `tb_um_promo_schedule`
ORDER BY `CLIENT_PROMO_ID` DESC
LIMIT 0 , 30

Upvotes: 2

Related Questions