Re: SQL Date Range

[CRPence <CRPbottle@xxxxxxxxx>]

  Seems odd there would be any problem if the XMittalDate were 
truly a DATE data type column as originally stated.  In that case, 
the format should be moot; i.e. assuming the variables assigned the 
literals of format mm/dd/yyyy are properly recognized as the 
expected date values.  I would expect the undesirable effect only if 
the query performed an implicit cast of the XMittalDate column to a 
character string.?

Regards, Chuck

Shannon ODonnell wrote:
> You're right Rick. I actually just discovered that a few minutes
> ago.
>
> I'll find a good date conversion routine I can copy from on 
> Google for my PHP script to take care of that.
>
> Thanks for pointing that out! It was only luck that I happened to
>  go past year end checking it just now. I wasn't even looking for
> that, I was testing something else when I discovered it. I could
> just as easily have missed it until I got a lot deeper into
> this.
>
> Rick Chevalier wrote:
> > This doesn't directly address your question but I believe you 
> > have a bug in your statement. Given the data example you have
> > you will have issues with your date range if it is truly in 
> > MM/DD/YYYY format. For example, if you wanted records crossing
> > a year end your end date is less than your start date.
> >
> > We have a lot of dates like this and had to create a user
> > defined function to convert them to ISO for comparison. It can
> > be done without the UDF but it's pretty ugly.
> >
> > Shannon ODonnell wrote:
> > > I want to select a group of dates based on date range in a
> > > PHP app from a MySQL database.
> > >
> > > Assume the table name is xmittalheader and the date field is 
> > > named: XMittalDate and is in the format in the MySQL table
> > > like this: 11/02/2009
> > >
> > > Further assume $startDate and $endDate are being brought into
> > > the PHP via a POST from a form.
> > >
> > > Would this be the correct syntax?
> > >
> > > $results = mysqli_query($db, "SELECT * FROM xmittalheader
> > > WHERE XMittalDate BETWEEN ".$startDate." and ".$endDate);