The Perl solution would be to get rid of the cast, and simply test date_completed in Perl:

$sth=$dbh->prepare(select ....);
$sth->execute;
$res=$sth->fetchrow_hashref;
$$res{is_complete}=defined($$res_{date_completed});
[download]

Given "Invalid character value for cast specification" and that it works when not using Perl, I'd guess "\t" or "\r" is the problem. Seems unlikely. You should probably also dump (for example, using Data::Dumper) the string to make sure there aren't more unusual characters in there that you aren't seeing.

Since this is a static query, that Query Analyzer and Perl have different results is surprising. Try rewriting the SQL to eliminate the CASE, or use the other form of the CASE. CASE ... END AS is_complete is the same and might cause problems. Also, you might want to parse the date in Perl.

With what you've described, I can't see what would cause your problem. I tried it.

(Since you didn't provide any schema or sample data I invented some.)

-- T-SQL
create table milestones (
    [id] int not null,
    project_ID char(1) null,
    title char(1),
    details char(1) null,
    date_expected smalldatetime null,
    date_completed smalldatetime null
)
go
insert into milestones values (1,'a','a','a','2004-01-01', '2004-01-01
+')
insert into milestones values (1,'b','a','a','2004-01-02', '2002-01-01
+')
insert into milestones values (1,'c','a','a','2004-02-10', null)
insert into milestones values (1,'d','a','a', null, null)
insert into milestones values (1,'e','a','a','2004-11-11', null)
go    
select * from milestones
go
-- Milestones now contains -
id          project_ID title details date_expected          date_compl
+eted     
----------- ---------- ----- ------- ---------------------- ----------
+---------
1           a          a     a       2004-01-01 00:00:00    2004-01-01
+ 00:00:00
1           b          a     a       2004-01-02 00:00:00    2002-01-01
+ 00:00:00
1           c          a     a       2004-02-10 00:00:00    NULL
1           d          a     a       NULL                   NULL
1           e          a     a       2004-11-11 00:00:00    NULL

(5 row(s) affected)

# Perl code
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:ODBC:driver=SQL Server;database=pubs;serve
+r=(local)", 'sa', 'mypassword') or die "can't connect";

my $q = qq{
    
select
    ID,
    project_ID,
    title,
    details,
    DATEPART(dd, date_expected),
    DATEPART(mm, date_expected),
    DATEPART(yyyy, date_expected),
    DATEPART(dd, date_completed),
    DATEPART(mm, date_completed),
    DATEPART(yyyy,date_completed),
    is_complete = case 
        when date_completed is null then 0
        else 1
    end
from
    MILESTONES
where
    ID = 1
    
};
my $sth= $dbh->prepare($q);

$sth->execute;

while (my @row = $sth->fetchrow_array) {
    $_ ||= 'NULL' for @row;    
    print "@row\n";
}

$sth->finish;

$dbh->disconnect;

__END__
d:\tmp>Test.pl
1 a a a 1 1 2004 1 1 2004 1
1 b a a 2 1 2004 1 1 2002 1
1 c a a 10 2 2004 NULL NULL NULL NULL
1 d a a NULL NULL NULL NULL NULL NULL NULL
1 e a a 11 11 2004 NULL NULL NULL NULL
[download]