sql statements based on ##/##/#### format.

[njdubois]

My dates are in the format of month/day/year.  ##/##/####, with no leading zeros, so 7 is 7, not 07.

 

I will be provided to name, a start date, and an end date.

 

so, Nick, 5/5/2012, 5/15/2012

 

I need to pull all records from a table from the start date, to the end date, where name = nick.

 

What would my sql statement look like?

 

I've looked all over the internet, but everything says to use a different date format, but I've already got a massive database, with many different applications using and writing to said database.

 

Many thanks for the assistance!

Nick

[njdubois]

Can't locate the edit link?

 

Guess I should have included this information.

 

My current solution involves giving the user options, month week or day.

 

Month is easy, I use LIKE $month .'/%%/'.$year

 

But for week, I do a for loop for each day and build a long sql statement using or

select * from table where name = 'nick' AND date='5/5/2012' OR name='nick' AND date='5/6/2012'

 

but this seems like to much work?  The problems start when they want more days than a week worth of data pulled, or maybe only a handful of days with in a week.  what if its 2 dates that span months?

 

Thanks for taking the time to read this!

 

Nick

[Pikachu2000]

You really should add a new field, update it with the correct format, and start changing your code to use the correct DATE format. In the meantime, STR_TO_DATE().

[ignace]

Selecting month/year:

SELECT * FROM sometable WHERE MONTH(STR_TO_DATE(`date`)) = $month AND YEAR(STR_TO_DATE(`date`)) = $year

 

Selecting a specific week:

SELECT * FROM sometable WHERE WEEK(STR_TO_DATE(`date`)) = $week AND YEAR(STR_TO_DATE(`date`)) = $year

 

Like pikachu already mentioned you should add a new column with the proper date format (and data-type) and use that onwards.