in reply to DBD::ODBC, Access, date comparisons

Two thoughts come to mind, both SQL thoughts though.

Cast ClearDate into a date type instead of a date time type. Also use CURRENT_DATE in the place of the placeholder if you want just today in the format the database wants it.

The other option if you can't cast the type into the one you want is to use < and >= in two where clauses

... WHERE ClearDate >= '2003/03/20 0:00' AND < '2003/03/21 0:00' ...
#or express this with a between clause

[download]

The typecast will be the best solution since you get the database to do the conversions so it likes whats going on.

Note: Of course you would parse up the 2 dates and put them in placeholders resulting in an approprate where for each query.

Replies are listed 'Best First'.
Re: Re: DBD::ODBC, Access, date comparisons
by finni (Beadle) on Mar 20, 2003 at 16:30 UTC
    I'm using placeholders because it's not always going to be 'today' that I want to match. The eventual statement will be a BETWEEN clause.

    Maybe I need to just make my dates go to a minute before and a minute after midnight, on the appropriate dates.

    The thing that kills me is if I do this 

    WorkOrd.ClearDate  >= #3/19/2003#

    [download]
    it works correctly. Trying to get the hash-marks into the placeholder is what causes the ODBC driver to bomb though; I don't know what's happening in there.
[reply]
[d/l]

      Have you tried WHERE WorkOrd.ClearDate >= #?# as your clause fragment?

[reply]
        Yup - that was one of the first things I tried. Bomb:
        DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver
+] Syntax error in date in query expression 'WorkOrd.StatCode5 = 'C' a
+nd ( 
        WorkOrd.ClearDate is NULL or ( 
            WorkOrd.ClearDate  BETWEEN #Pa_RaM000# and #Pa_RaM001#
        )
    )'. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at O:\Scripts\P
+erl\ServiceReports\test-av-citigroup.pl line 54.
Uncaught exception from user code:
    Could not execute statement. Error:[Microsoft][ODBC Microsoft Acce
+ss Driver] Syntax error in date in query expression 'WorkOrd.StatCode
+5 = 'C' and ( 
        WorkOrd.ClearDate is NULL or ( 
            WorkOrd.ClearDate  BETWEEN #Pa_RaM000# and #Pa_RaM001#
        )
    )'. (SQL-37000)(DBD: st_execute/SQLExecute err=-1)
Report run on 2003-03-19      Report runs from 2003-03-19 to 2003-03-2
+0.

        [download]
[reply]
[d/l]
Re: Re: DBD::ODBC, Access, date comparisons
by finni (Beadle) on Mar 20, 2003 at 16:41 UTC
    Also, I couldn't find a simple SQL way to cast ClearDate to a Date field - I've read the ODBC SQL date functions, and unless I'm missing something, I think I'm out of luck. All the examples I could find involved writing a VB function to do it, which would be silly to emulate here. Any Access SQL wizards? 

    my $report_date        = "\#".&UnixDate(&ParseDate("3/19/2003"), "%m%/
+%d%/%Y")."\#";
my $start_date        = "\#".&UnixDate(&ParseDate("3/19/2003"), "%m%/%
+d%/%Y")."\#";
my $end_date        = "\#".&UnixDate(&ParseDate("3/20/2003"), "%m%/%d%
+/%Y")."\#";

my ($DaysOpen, $Priority, $Status)    = undef;
my ($WO2, $Ticket, $DateOpened, $ClearDate, $SiteCode,  $Problem1, $Si
+teCall1, $Dispatch, $Troubleshooting, $Solution1 )= undef;

$sqlstatement= qq (
    SELECT WorkOrd.WO2, WorkOrd.ClearDate
    from WorkOrd
    where WorkOrd.StatCode5 = 'C' and ( 
        WorkOrd.ClearDate is NULL or ( 
            WorkOrd.ClearDate  BETWEEN ? and  ?
        )
    )
);

print("Report run on $report_date \t Report runs from $start_date to $
+end_date.\n");

$sth = $dbh->prepare($sqlstatement) or  die "Could not prepare stateme
+nt. Error:$sth->errstr()\n";
$sth->execute($start_date, $end_date) or  die "Could not execute state
+ment. Error:$sth->errstr()\n";

    [download]
    This seems to insert the hash-marks properly into the strings, because the print("Report...") statement works correctly, but ODBC poops on me like this
    DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver
+] Data type mismatch 
in criteria expression. (SQL-22005)(DBD: st_execute/SQLExecute err=-1)
+ at O:\Scripts\Perl\ServiceReports\av-citigroup.pl line 54.

Uncaught exception from user code:
    Could not execute statement. Error:DBI::st=HASH(0x21181c8)->errstr
+()
Report run on #03/19/2003#      Report runs from #03/19/2003# to #03/2
+0/2003#.

    [download]
    If I don't use placeholders, I get this: 
    Report run on #03/19/2003#      Report runs from #03/19/2003# to #03/2
+0/2003#.
38706    2003-03-19 14:17:01
38747    2003-03-19 18:40:42
38803    2003-03-19 15:26:40
38848

    [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom