[freetds] Unexpected results with null fields

KERTEL - Maxime MARAIS mmarais at kertel.com
Tue Jun 6 07:19:01 EDT 2006


Hello,

I am running a query looking like the following one (the one I use also 
select other fields):

SELECT id, isnull(
	right('0'+convert(varchar,day(max(T.gdh))),2)
	+right('0'+convert(varchar,month(max(T.gdh))),2)
	+convert(varchar,year(max(T.gdh)))
	+right('0'+convert(varchar,datepart(hour,max(T.gdh))),2)
	+right('0'+convert(varchar,datepart(minute,max(T.gdh))),2)
	,'010119700000')
FROM MyTable T
GROUP BY id

with MyTable(id int not null, gdh datetime null).

I expect to get either the biggest date (T.gdh) for each id in MyTable 
ddmmyyyyhhmm formated or '010119700000' when max(T.gdh) if null

When I run this query from MS Query Analyser, I get the expected 
results: dates looking like ddmmyyyyhhmm or '010119700000'.

When I run this query from a Perl script using ODBC + a DSN with MSSQL 
Driver on Windows I also get the expected results.

But, when I run this query from a Perl script using DBD::Sybase + 
FreeTDS on Linux I get dates ddmmyyyyhhmm formated or '0000' when 
max(T.gdh) is null. It appears the '0000' comes from the right('0'+....) 
functions: Replaced the first right('0'...) with right('A'...) and i get 
some 'A000' instead of '0000'.

Any idea about this unexpected behaviour?

Regards,

-- 
Maxime MARAIS
Kertel.




More information about the FreeTDS mailing list