MsSQL Books


Tuesday, April 18, 2006

Convert string to date in mySQL

if you have a date column in a table that has a string datatype, it is impossible to query or find the record between 2 durations of date. for example, you have a table "myTable"

myTable

---------------------------
Column  | Type
---------------------------
date            | varchar

it is impossible to query like this
SELECT * FROM myTable WHERE date>='@dtFrom' AND date<='@dtTo' the correct way to get the record between the 2 dates is like this SELECT * FROM myTable WHERE STR_TO_DATE(date,'%d/%m/%Y') <= '@dtFrom' AND STR_TO_DATE(date,'%d/%m/%Y') <= '@dtTo' STR_TO_DATE(date,'%d/%m/%Y') the type '%d/%m/%Y' is depend on how you store the date into the table. In my case, I store it as 18/04/2006 more help can be found here - dev.mysql.com

5 comments:

Anonymous said...

datetime? seems more like date to me... what about the time?

mrzoab said...

oops... sorry...
you're right, this is only for date.

Unknown said...

Thanks, the post help me greatly! :D

mrzoab said...

I'm glad to help... :D

Anonymous said...

Please correct the title of this post to
'Convert string to date in mySQL'