--- David Wheeler <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> Given the below script (using DBD::SQLite 1.08, which
> uses SQLite
> 3.1.3), the output is just:
>
> +0 Cast: 2005-03-22T00:00:00
>
> I'm wondering, however, if unary + shouldn't also be able
> to cast an
> expression to a number...shouldn't it?
>
> Thanks,
>
> David
>
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> use constant SQLITE_FILE => shift;
>
> my $dbh = DBI->connect_cached(
> 'dbi:SQLite:dbname=' . SQLITE_FILE, '', '', {
> RaiseError => 1,
> PrintError => 0,
> }
> );
>
> END {
> $dbh->disconnect;
> $dbh->rollback;
> }
>
> $dbh->begin_work;
> $dbh->do("CREATE TABLE foo (a TEXT)");
> $dbh->do("INSERT INTO foo
> VALUES('2005-03-22T00:00:00')");
> my $sth = $dbh->prepare("SELECT * FROM foo WHERE
> (substr(a, 6, 2) =
> ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "No Cast: $row->[0]\n";
> }
>
> $sth = $dbh->prepare("SELECT * FROM foo WHERE (+substr(a,
> 6, 2) = ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "Unary Cast: $row->[0]\n";
> }
>
> $sth = $dbh->prepare("SELECT * FROM foo WHERE (substr(a,
> 6, 2)+0 = ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "+0 Cast: $row->[0]\n";
> }
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for? SQLite can give you an offset from the Unix
epoch from a properly formatted date-time string, but I
don't think you could get that using "select * from ...".
If you were looking for 20050322, SQLite can do that for
you, too. Have a look at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
-Clark
