Check the data type of the 'date' column. Your SQL statement should work for the DATE type, but if you are storing the date as a string, the return will be alphabetized. You should have shown us the order you get.

Update: Aha! My bad, I should have noticed. 'date' is illegal as a column name because it's a datatype. Just pick another name.

After Compline,
Zaxo

The data type for the date column is DATE. I can't show you the order the rows are returned because I'm at a different computer right now.

I do believe however, the rows returned were in the same order as stored in the database. Is 'date' a valid column name? I really don't have any ideas what's wrong here. Thanks for the help.

UPDATE: The original poster said "my sql", Mysql is just my imagination.

Make a database with a table like so:

create table mileage ( day date, purpose int);
[download]

It is good to try to condense problems like these to the smallest
free standing code that demonstrates the problem. When
you do this you may find the problem in your code. When
doing mixed language developement it is easier to make syntactic
mistakes and more likely that your tools will not report
them well.

#!/usr/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=t", "use", "pw")
        or die "cant connect: $!\n";

my $s = $dbh->prepare(
        "SELECT day, purpose
        FROM mileage
        WHERE day BETWEEN ? and ?
        ORDER BY day"
);

$s->bind_param(1, "1999-01-01", {TYPE=>DBI::SQL_VARCHAR} );
$s->bind_param(2, "1999-12-31", {TYPE=>DBI::SQL_VARCHAR} );


$s->execute;

while ( my @row = $s->fetchrow_array)
{
    print "$row[0], $row[1], \n";
}
$s->finish;
$dbh->disconnect;
[download]

This really isn't a perl question. At all. Not even a bit. Just because you are using perl to execute the SQL doesn't make it a perl thing (99.9% of the time anway). Please, for yours and our sanity go ask on your database's mailing list. A snippet of SQL's behaviour can and will be different between different databases. On it's face the behaviour looks odd but it's also entirely possible that this is understandable from the perspective of your database. Right now we don't know if this is PostgreSQL or MySQL or what your table schema and data looks like.

So... either just turn that into a completely RDMBS oriented question (since that what it really is) or ask the question in an appropriate forum. Just don't pretend it's a perl question.

Update: Fixed formatting

I disagree. Quoth vroom himself in Friar:

The monastery tolerates general off-topic questions occasionally, and some monks even know a little bit about operating systems, web servers, databases, and the like. Perl is a glue language, and occasionally it has to interact with other things. Sometimes the problem is with the poster's code, sometimes not. If the question has come up before, it will probably come up again. Let the archives take it.

Makeshifts last the longest.

Well... I disagree with your disagreement. There's a whole lot of context missing in that question which just nixed it for me. I really got the impression that the poster either didn't get it and thinks it's a perl question or was just leaving some perl crumbs onto a SQL only problem.

It's entirely possible for a person who knows ANSI SQL would notice that DATE is reserved but in general the entire behaviour in question is going to very RDBMS specific. There just isn't any fair way for the people here to guess at what the RDBMS was really doing since no one knew what it is. It's sort of like asking about some specific output from ps(1). While we all know what that is and in general it acts (I suppose) similarly from UNIX to UNIX the actual options are going to be different and it isn't really fair to expect any PM to be able to answer the question.

I'm also ill and cranky today so that's partly why I'm less tolerant than I might be normally. I thought I was fair - mostly.

This mod may be able to force the ordering:

'SELECT number, date, miles, purpose
FROM mileage
WHERE date BETWEEN ? and ?
ORDER BY 2'

If that doesn't work, try:

'SELECT number, trunc(date), miles, purpose
FROM mileage
WHERE date BETWEEN ? and ?
ORDER BY trunc(date)'

because date formats are strange and they often contain unfathomable data!

@ncientgoose