--- 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