Thanks guys, It seems to fail if I use it in a union where there are multiple select stmts. Is it because I am using too many bind variables? I am not sure why but here is the sql code that it fails on. All the other code is the same, only sql code is different, The ERROR I wrote on the bottom of the code: 
my @intervals = (
["{ts'2012-07-01 00:00:00.000'}", "{ts'2012-07-02 00:00:00.000'}","{ts
+'2012-07-01 00:00:00.000'}", "{ts'2012-07-02 00:00:00.000'}"],
["{ts'2012-07-02 00:00:00.000'}", "{ts'2012-07-03 00:00:00.000'}","{ts
+'2012-07-02 00:00:00.000'}", "{ts'2012-07-03 00:00:00.000'}"]);


my $dbh = DBI-> connect('dbi:ODBC:DSN=WCPM-DB1;UID=TEST;PWD=pass') or 
+die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";

my $sql2 = <<EOSQL;
SELECT *, DATEDIFF(n,MIN_TIME,MAX_TIME) AS MINS_DIFF 
        FROM
            (SELECT Login_ID, MIN(TimeStamp) AS MIN_TIME,MAX(TimeStamp
+) AS MAX_TIME
            FROM 
                (SELECT Login_ID,  AuditChrt_TimeStamp AS TimeStamp,
                    Patient_ID
                FROM         
                    (SELECT Login_ID, AuditChrt_TimeStamp, Patient_ID
                    FROM TopsData.dbo.AUDT_AuditChrt
                    WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZ
+Z0006J','ZZZZZ0006P','ZZZZZ0007A')
                                    AND AuditChrt_TimeStamp > ?    
                                    AND AuditChrt_TimeStamp < ?) AS A
    UNION ALL
            SELECT Login_ID, AuditMedication_TimeStamp AS TimeStamp, P
+atient_ID
            FROM 
                (SELECT Login_ID, AuditMedication_TimeStamp, Patient_I
+D
                FROM TopsData.dbo.AUDT_AuditMedication
                 WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZZ00
+06J','ZZZZZ0006P','ZZZZZ0007A')
                                    AND AuditMedication_TimeStamp > ? 
+       
                                    AND AuditMedication_TimeStamp < ?)
+ AS B
            ) AS A
    GROUP BY Login_ID) AS A;                            
EOSQL
my $sth = $dbh->prepare($sql2);

foreach my $interval (@intervals) {
    $sth->execute(@$interval);
    my @row;
    while (@row = $sth->fetchrow_array) {  # retrieve one row at a tim
+e
        print  join(", ", @row), "\n";

ERROR: DBD::ODBC::st failed: Conversion failed when converting data an
+d/or time from character string.

[download]

In reply to Re^4: SQL query using elements from array by AllPaoTeam
in thread SQL query using elements from array by AllPaoTeam

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.