Re: SQL bug, ISNULL function with smalldatetime

From: oj (nospam_ojngo_at_home.com)
Date: 02/10/05 

Date: Wed, 9 Feb 2005 16:06:43 -0800

>>WHERE 2005-02-10T00:54:00.000 >= 2005-02-10T00:54:17.123

Ah, didn't even think about the rounding and re-implicit conversion to
datetime. Tks. 

-- 
-oj
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message 
news:gh8l0151vkpi557b6nigvl2npok6sa95v7@4ax.com...
> On Wed, 9 Feb 2005 15:21:04 -0800, shovem wrote:
>
>>My version is
>>
>>Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05
>>Copyright (c) 1988-2003 Microsoft Corporation  Developer Edition on 
>>Windows
>>NT 5.1 (Build 2600: Service Pack 2)
>>
>>For @dateParam, I'm setting it to getUTCDate()
>>I forgot to include that code:
>>
>>DECLARE @dateParam datetime
>>SET @dateParam = getUTCDate()
>
> Hi shovem,
>
> This is not a bug, but working as designed.
>
> From Books Online, section on ISNULL:
>
> Return Types
> Returns the same type as check_expression.
>
> (note that this is an important -and often unexpected!- differnece between
> ISNULL and COALESCE)
>
> So ISNULL(tdq.test, @dateParam) will return a smalldatetime. If tdq.test
> is NULL and @dateParam is 2005-02-10T00:54:17.123, it will be converted to
> smalldatetime, rounded to the nearest full minute: 2005-02-10T00:54.
>
> The complete WHERE clause reads
> WHERE ISNULL(tdq.test, @dateParam) >= @dateParam
> Here, a smalldatetime (the result of ISNULL) is compared to a datetime
> (@dateParam). Rules of datatype precedence say that the smalldatetime has
> to be converted to datetime. Of course, the seconds that were lost during
> the previous conversion won't return, so the final comparison is
> WHERE 2005-02-10T00:54:00.000 >= 2005-02-10T00:54:17.123
> And this, of course, evaluates to false - therefor, the row is not
> returned.
>
> The fix is simple: either declare the parameter as smalldatetime, or
> create the column as datetime. And if either of these solutions is not
> possible, then use explicit casting instead of relying on implicit
> conversions.
>
> Best, Hugo
> -- 
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: SQL bug, ISNULL function with smalldatetime
    ... >For @dateParam, ... will return a smalldatetime. ... a smalldatetime is compared to a datetime ... the previous conversion won't return, ...
    (microsoft.public.sqlserver.programming)
  • Re: Date Problems - ASP/SQL
    ... a DATETIME column does NOT store DD/MM/YYYY format. ... DECLARE @dt SMALLDATETIME ... data> type to a datetime data type resulted in an out-of-range datetime value. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Time Conversion
    ... You could do the conversion manually. ... TimeSpan span = TimeSpan.FromSeconds; ... DateTime dateTime = base + span; ... which is Time in seconds since UTC 1/1/70 in theory. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Formatting DateTime with an offset
    ... DateTime a is sealed value type so you can not extend it. ... would like my users to be able to specify their prefered TimeZone. ... I have written the code to do the timezone conversion. ... rather have DateTime.ToString go through some kind of formatting that ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: convert datetime to smalldatetime
    ... CREATE TABLE Source(col1 Datetime) ... CREATE TABLE Destination(col1 smalldatetime) ... www.SQLDTS.com - The site for all your DTS needs. ... > I am wondering if anyone can tell me how to convert a datetime field to ...
    (microsoft.public.sqlserver.dts)