SQL Statement help...

[azuro]

Let say i have a table:

Create Table records(
Name char(20),
Date char(20),
Primary Key (Name));

Date is in format 2005/07/13
If I want to query the database and retrieve data that have date between 2005/06/12 and 2005/07/12, how can I do it with regular expression?

For the Date attribute, if the datatype is Timestamp instead, how would I do it? Thanks.
BTW, I'm using postgresql and programming it with perl.

 

[doh]

Why aren't you using a date column?

 

[azuro]

if I use the date column, how do I find the records between 2 dates?
Will the following perl code works?

$date1 = "2005/11/12";
$date2 = "2005/12/22";

$sth = $dbh->prepare( q{
SELECT name
FROM records
WHERE date >= ? AND date <= ?
})

$rc = $sth->execute($date1, $date2);

 

[svet-am]

I will naively assume that you're using PHP or a similar language in conjunction with your SQL.

If that's the case, PHP has a built in function (date()) that, when passed the proper parameters, will take a standard calendar date and return the UNIX time stamp (which is strictly in seconds). So, all you need to do is convert both dates and then do a simple comparison.

http://us2.php.net/manual/en/function.date.php