help with mysql query varchar to date

[syntax101]

i have a table and my cc_date(varchar type) and the date format is 1/31/2008, 2/28/2008

im having trouble selecting them by range.

 

my query is

 

select * from cc where cc_date >= ?

 

can anybody help me how to query that varchar type to date(format) pls help im using mysql 5.0

[Mchl]

First of all, don't store dates as varchar. This is just one of the problems you will experience if you do.

The DATE, TIME, DATETIME, TIMESTAMP field types were introduced for a reason.

 

Second: not all is lost. You can use STR_TO_DATE() mysql function.

 

 

[syntax101]

yeah im having problems now. can u give me a sample query based on my sample format i tried that but i cannot get it working. please. i will learn my mistake. :'(

[syntax101]

here is my query

 

SELECT cc_num, cc_jobnumber, emp_name, cc_entry, cc_cc

FROM cc

WHERE STR_TO_DATE('cc_date', '%m/%d/%Y') >= ? (1/1/2008)

 

cc_date contains

 

1/1/2007

1/1/2008

1/2/2007

8/7/2008

1/31/2007

 

result must be

1/1/2008

8/7/2008

 

can anybody tell me whats wrong. i cannot display the result above

 

[Mchl]

select * from cc where STR_TO_DATE(cc_date,'%m/%d/%Y') >= //here input date in mysql format (for example YYYY-MM-DD)

[syntax101]

SELECT cc_num, cc_jobnumber, emp_name, cc_entry, cc_cc, cc_date

FROM cc

WHERE STR_TO_DATE(cc_date, '%m/%d/%Y') >= STR_TO_DATE(?,

    '%m/%d/%Y')

 

here the final query and it works thank you Mchl

[Mchl]

Try to convert your dates to proper format. It should not be difficult, and you'll save yourself a lot of problems,time and storage space in the future.

[syntax101]

yes MChl thanx again.

[diasansley]

thanks works fab