freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
- From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
- To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] Unexpected results with null fields
- Date: Tue, 6 Jun 2006 13:41:34 +0200
>
> 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?
>
Hi!
yes, it depends on settings. ODBC like query analyser concatenate
NULLs using empty string while dblib (and ctlib) by default yield NULL
this means that
SELECT 'x' + NULL
return 'x' under ODBC and NULL under ctlib.
The setting is SET ANSI_NULLS (search it under query analyser help)
freddy77
-
[freetds] Unexpected results with null fields,
KERTEL - Maxime MARAIS, 06/06/2006
- <Possible follow-up(s)>
-
Re: [freetds] Unexpected results with null fields,
ZIGLIO, Frediano, VF-IT, 06/06/2006
-
Re: [freetds] Unexpected results with null fields,
KERTEL - Maxime MARAIS, 06/06/2006
- Re: [freetds] Unexpected results with null fields, KERTEL - Maxime MARAIS, 06/06/2006
-
Re: [freetds] Unexpected results with null fields,
KERTEL - Maxime MARAIS, 06/06/2006
Archive powered by MHonArc 2.6.24.