finni has asked for the wisdom of the Perl Monks concerning the following question:

After slapping Excel around successfully, I'm now getting pummeled by Access. I'm using ActiveState Perl and DBD::ODBC. There is an Access field that is a date/time field; I need the get the full date/time data when I query it. BUT, for the SQL query, I only want to compare the date portion, not the date/time. 
use Win32::ODBC;
use DBI;
use Date::Manip;
&Date_Init("TZ=EST");
my $now = localtime();
my $start_date        = &UnixDate(&ParseDate("$now"), "%m%/%d%/%Y");
$sqlstatement= qq (
    SELECT WorkOrd.WO2, WorkOrd.ClearDate
    from WorkOrd
    where WorkOrd.StatCode5 = 'C' and ( 
        WorkOrd.ClearDate is NULL or ( 
            WorkOrd.ClearDate  = ?
        )
    )
);
$sth = $dbh->prepare($sqlstatement) or  die "Could not prepare stateme
+nt. Error:$sth->errstr()\n";
$sth->execute($start_date) or  die "Could not execute statement. Error
+:$sth->errstr()\n";
$sth->bind_columns(    \$WO2, \$ClearDate );

[download]
This only returns things that are NULL in ClearDate, not things that were cleared today. I've tried playing with different UnixDate formats, and that gave the same (incomplete) results. I tried encasing the date in Access-style #, but that bombed with DBS::ODBC errors. I'm not making much headway here; I'd love some hints.

Replies are listed 'Best First'.
Re: DBD::ODBC, Access, date comparisons
by dga (Hermit) on Mar 20, 2003 at 16:22 UTC

    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.

[reply]
[d/l]
      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]
      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]
Re: DBD::ODBC, Access, date comparisons
by finni (Beadle) on Mar 20, 2003 at 18:12 UTC
    OK - I can do it with either 
    {d 'YYYY-mm-dd'}
or
#mm/dd/YYYY#

    [download]
    directly in the SQL statement. But, if I use placeholders, it craps out on me. The variables I'm using in the $sth->execute() command look correct if I print them out.

    So, I'm a little stumped.

    UPDATE

    http://aspn.activestate.com/ASPN/Mail/Message/1438444

    bind_param - looks like it could be my ticket.

    I'm going to finally go eat some lunch now. I always let stuff slip when I'm stumped like this. Then, I'm starving, desperate, and in a bad mood. Typically, a user will now come up to me, and ask me to do a file restore from tape, because someone deleted something that SHOULD NOT have been in the public directory in the first place.

    When I get back, I'll try the bind_param and see if I get any luck. In the meantime, if someone has any comments or encouragement, it would be great to see when I get back with a higher blood-sugar level.

[reply]
[d/l]
      Did you ever get it to work? I have the same problem but I haven't tried bind_param.
[reply]

Back to Seekers of Perl Wisdom