formulating an UPDATE and writing WHERE clauses for the columns in a table.
(I'm in a .net aspx.vb program using a data adapter) In a specific record a
datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign (=).
Microsoft SQL Server returns date and time values exactly matching the
month, day, and year, and at the precise time of 12:00:00:000 A.M.
(default)."
So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
TTina,
Try:
SELECT...
FROM ...
WHERE COL >= '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl...
> I'm writing a program that is dynamically writing SQL for SQLServer. I'm
> formulating an UPDATE and writing WHERE clauses for the columns in a
> table. (I'm in a .net aspx.vb program using a data adapter) In a specific
> record a datetime field has '7/20/2005 08:07:58 AM'
> my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
> causes the update to fail apparently because it is a mismatch. BOL says
> "To search for an exact match on both date and time, use an equal sign
> (=). Microsoft SQL Server returns date and time values exactly matching
> the month, day, and year, and at the precise time of 12:00:00:000 A.M.
> (default)."
> So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
> fail too. and so does 12:00:00:000 AM
> How is this supposed to be done?
> Thanks,
> T
>|||Well that is a circumvention that would probably work but...
I discovered that the date in the database is actually 7/20/2005 8:07:58:197
AM and that ADONET somewhere in the dataadapter is eliminating the :197 and
there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not sure
but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> Tina,
> Try:
> SELECT...
> FROM ...
> WHERE COL >= '20052007' AND COL <'20052108'
> HTH
> Jerry
> "Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
> news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl...
>> I'm writing a program that is dynamically writing SQL for SQLServer. I'm
>> formulating an UPDATE and writing WHERE clauses for the columns in a
>> table. (I'm in a .net aspx.vb program using a data adapter) In a
>> specific record a datetime field has '7/20/2005 08:07:58 AM'
>> my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
>> causes the update to fail apparently because it is a mismatch. BOL says
>> "To search for an exact match on both date and time, use an equal sign
>> (=). Microsoft SQL Server returns date and time values exactly matching
>> the month, day, and year, and at the precise time of 12:00:00:000 A.M.
>> (default)."
>> So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
>> fail too. and so does 12:00:00:000 AM
>> How is this supposed to be done?
>> Thanks,
>> T
>>
>|||This is a multi-part message in MIME format.
--090102040500040805090004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No, if SQL Server receives a statement comparing a datetime column with
a string literal that implicitly converts to a datetime datatype (such
as "WHERE col1 = '20050720 08:07:58.197'") then it honours the request.
It doesn't truncate any characters from the string literal (unless
you're type casting into a smalldatetime instead of a datetime because
smalldatetime datatypes don't store millisec or sec info). I assume
you're not doing explicit conversions and are just leaving it up to SQL
Server to implicitly convert the string (you don't mention this in your
post).
I would say that the millisec info is getting chopped off before it hits
the SQL Server. If you really want to be sure you can run SQL Profiler
and watch the statement as SQL Server receives it to see exactly what is
getting through the intermediate layers (i.e. ADO, etc.).
Also, you ought to express string literals that convert to datetime data
as "yyyymmdd hh:nn:ss.000". All the other formats (where the month is
not spelled out with alpha chars rather than numeric chars) are ambiguous.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tina wrote:
>Well that is a circumvention that would probably work but...
>I discovered that the date in the database is actually 7/20/2005 8:07:58:197
>AM and that ADONET somewhere in the dataadapter is eliminating the :197 and
>there for causing my subsequent WHERE to fail.
>So, it might be an adonet issue and not a sql server issue - i'm not sure
>but I don't believe it should be cutting off the 197.
>Thanks for your circumvention.
>T
>
>"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>
>>Tina,
>>Try:
>>SELECT...
>>FROM ...
>>WHERE COL >= '20052007' AND COL <'20052108'
>>HTH
>>Jerry
>>"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
>>news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl...
>>
>>I'm writing a program that is dynamically writing SQL for SQLServer. I'm
>>formulating an UPDATE and writing WHERE clauses for the columns in a
>>table. (I'm in a .net aspx.vb program using a data adapter) In a
>>specific record a datetime field has '7/20/2005 08:07:58 AM'
>>my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
>>causes the update to fail apparently because it is a mismatch. BOL says
>>"To search for an exact match on both date and time, use an equal sign
>>(=). Microsoft SQL Server returns date and time values exactly matching
>>the month, day, and year, and at the precise time of 12:00:00:000 A.M.
>>(default)."
>>So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
>>fail too. and so does 12:00:00:000 AM
>>How is this supposed to be done?
>>Thanks,
>>T
>>
>>
>>
>
>
--090102040500040805090004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No, if SQL Server receives a statement comparing a datetime column
with a string literal that implicitly converts to a datetime datatype
(such as "WHERE col1 = '20050720 08:07:58.197'") then it honours the
request. It doesn't truncate any characters from the string literal
(unless you're type casting into a smalldatetime instead of a datetime
because smalldatetime datatypes don't store millisec or sec info). I
assume you're not doing explicit conversions and are just leaving it up
to SQL Server to implicitly convert the string (you don't mention this
in your post).<br>
<br>
I would say that the millisec info is getting chopped off before it
hits the SQL Server. If you really want to be sure you can run SQL
Profiler and watch the statement as SQL Server receives it to see
exactly what is getting through the intermediate layers (i.e. ADO,
etc.).<br>
<br>
Also, you ought to express string literals that convert to datetime
data as "yyyymmdd hh:nn:ss.000". All the other formats (where the
month is not spelled out with alpha chars rather than numeric chars)
are ambiguous.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tina wrote:
<blockquote cite="miduORaOJQ1FHA.2132@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">Well that is a circumvention that would probably work but...
I discovered that the date in the database is actually 7/20/2005 8:07:58:197
AM and that ADONET somewhere in the dataadapter is eliminating the :197 and
there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not sure
but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:jspivey@.vestas-awt.com"><jspivey@.vestas-awt.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl">news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Tina,
Try:
SELECT...
FROM ...
WHERE COL >= '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:tinamseaburn@.nospammeexcite.com"><tinamseaburn@.nospammeexcite.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl">news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">I'm writing a program that is dynamically writing SQL for SQLServer. I'm
formulating an UPDATE and writing WHERE clauses for the columns in a
table. (I'm in a .net aspx.vb program using a data adapter) In a
specific record a datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign
(=). Microsoft SQL Server returns date and time values exactly matching
the month, day, and year, and at the precise time of 12:00:00:000 A.M.
(default)."
So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
T
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--090102040500040805090004--|||This is a multi-part message in MIME format.
--=_NextPart_000_000E_01C5D58F.43543980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
I'm not sure what I'm trying to say is getting through clear to you. =There is a date in my sql server table that is '07/20/2005 08:07:58:197 =AM'
If I select it using Query Analyzer the :197 comes through. If I select =it through E.M. it does NOT come through. If I select it using a .net =Data Adapter it does NOT come through. I don't think that E.M. and the =.Net data Adapters should be chopping off data.
I am wondering why such a thing happens and what could be done to get =around what looks to be a bug.
T
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:euPfYoR1FHA.972@.TK2MSFTNGP10.phx.gbl...
No, if SQL Server receives a statement comparing a datetime column =with a string literal that implicitly converts to a datetime datatype =(such as "WHERE col1 =3D '20050720 08:07:58.197'") then it honours the =request. It doesn't truncate any characters from the string literal =(unless you're type casting into a smalldatetime instead of a datetime =because smalldatetime datatypes don't store millisec or sec info). I =assume you're not doing explicit conversions and are just leaving it up =to SQL Server to implicitly convert the string (you don't mention this =in your post).
I would say that the millisec info is getting chopped off before it =hits the SQL Server. If you really want to be sure you can run SQL =Profiler and watch the statement as SQL Server receives it to see =exactly what is getting through the intermediate layers (i.e. ADO, =etc.).
Also, you ought to express string literals that convert to datetime =data as "yyyymmdd hh:nn:ss.000". All the other formats (where the month =is not spelled out with alpha chars rather than numeric chars) are =ambiguous.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
Tina wrote: Well that is a circumvention that would probably work but...
I discovered that the date in the database is actually 7/20/2005 =8:07:58:197 AM and that ADONET somewhere in the dataadapter is eliminating the :197 =and there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not =sure but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl...
Tina,
Try:
SELECT...
FROM ...
WHERE COL >=3D '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl...
I'm writing a program that is dynamically writing SQL for SQLServer. = I'm formulating an UPDATE and writing WHERE clauses for the columns in a table. (I'm in a .net aspx.vb program using a data adapter) In a specific record a datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate =3D '7/20/2005 08:07:58 AM' and =this causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign (=3D). Microsoft SQL Server returns date and time values exactly =matching the month, day, and year, and at the precise time of 12:00:00:000 A.M. (default)."
So I tried just putting WHERE LastUpdate =3D '7/20/2005' but that causes =a fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
T
--=_NextPart_000_000E_01C5D58F.43543980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Mike,
I'm not sure what I'm trying to say is =getting through clear to you. There is a date in my sql server table that =is '07/20/2005 08:07:58:197 AM'
If I select it using Query Analyzer the =:197 comes through. If I select it through E.M. it does NOT come =through. If I select it using a .net Data Adapter it does NOT come through. I =don't think that E.M. and the .Net data Adapters should be chopping off data.
I am wondering why such a thing happens =and what could be done to get around what looks to be a bug.
T
"Mike Hodgson"
--mike =hodgsonblog: http://sqlnerd.blogspot.com Tina wrote: Well that is a circumvention that would =probably work but...
I discovered that the date in the database is actually 7/20/2005 =8:07:58:197 AM and that ADONET somewhere in the dataadapter is eliminating the :197 =and there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not =sure but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" = wrote in message news:%235zU6UP1FHA.=3660@.TK2MSFTNGP15.phx.gbl...
Tina,
Try:
SELECT...
FROM ...
WHERE COL >=3D '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" wrote in message news:uJ%23GcRP1FHA.=2072@.TK2MSFTNGP12.phx.gbl...
I'm writing a program =that is dynamically writing SQL for SQLServer. I'm formulating an UPDATE and writing WHERE clauses for the columns in a table. (I'm in a .net aspx.vb program using a data adapter) In a specific record a datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate =3D '7/20/2005 08:07:58 AM' and =this causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign (=3D). Microsoft SQL Server returns date and time values exactly =matching the month, day, and year, and at the precise time of 12:00:00:000 A.M. (default)."
So I tried just putting WHERE LastUpdate =3D '7/20/2005' but that causes =a fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
T
--=_NextPart_000_000E_01C5D58F.43543980--|||This is a multi-part message in MIME format.
--020505030701070500060209
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
OK, this is not a bug but rather a design choice (at least that's the
case with SQLEM, I assume the designers of the SQLClient Data Adapter
made the same choice).
Think about this - if you store an inexact floating point number in a
database (eg. 73.87987409000010020000263...) and want to display it on
screen to a user, would you display "73.87987409000010020000263" or
would you make a design call to only display what you think the user
needs to know unless they ask otherwise? That is, would you perhaps
display "73.88" instead, even though that's not the actually value
stored in the DB? Seems like a reasonable decision to make. Well, the
designers of SQLEM decided not to display the millisec info when you ask
for a datetime column to be shown on screen with the "Open Table"
context menu. I don't believe this is a bug but rather what they chose
to display on screen when presenting a datetime column (the Open Table
feature, after all, is just supposed to be a quick "let's see what the
data looks like" type feature IMO - for any serious data interrogation
Query Analyser is the way to go). SQLEM has been around for 5 years +
the beta testing phase; I think a simple bug like that would have been
picked up since then.
I think it would be safe to assume that Microsoft made the same call
with the .NET SQLClient Data Adapter, or whichever component that is
layered on top of it that's responsible for dropping the millisec info.
I'd be surprised if you couldn't specify, in some property attribute of
some component in your VS project (like the data set or binding source
or display grid or one of those), a display format for the datetime data
such that it displays the millisec info when you ask for it. But that
might be a question better asked in the .NET newsgroups.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tina wrote:
> Mike,
> I'm not sure what I'm trying to say is getting through clear to you.
> There is a date in my sql server table that is '07/20/2005
> 08:07:58:197 AM'
> If I select it using Query Analyzer the :197 comes through. If I
> select it through E.M. it does NOT come through. If I select it using
> a .net Data Adapter it does NOT come through. I don't think that E.M.
> and the .Net data Adapters should be chopping off data.
> I am wondering why such a thing happens and what could be done to get
> around what looks to be a bug.
> T
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:euPfYoR1FHA.972@.TK2MSFTNGP10.phx.gbl...
> No, if SQL Server receives a statement comparing a datetime column
> with a string literal that implicitly converts to a datetime
> datatype (such as "WHERE col1 = '20050720 08:07:58.197'") then it
> honours the request. It doesn't truncate any characters from the
> string literal (unless you're type casting into a smalldatetime
> instead of a datetime because smalldatetime datatypes don't store
> millisec or sec info). I assume you're not doing explicit
> conversions and are just leaving it up to SQL Server to implicitly
> convert the string (you don't mention this in your post).
> I would say that the millisec info is getting chopped off before
> it hits the SQL Server. If you really want to be sure you can run
> SQL Profiler and watch the statement as SQL Server receives it to
> see exactly what is getting through the intermediate layers (i.e.
> ADO, etc.).
> Also, you ought to express string literals that convert to
> datetime data as "yyyymmdd hh:nn:ss.000". All the other formats
> (where the month is not spelled out with alpha chars rather than
> numeric chars) are ambiguous.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tina wrote:
>>Well that is a circumvention that would probably work but...
>>I discovered that the date in the database is actually 7/20/2005 8:07:58:197
>>AM and that ADONET somewhere in the dataadapter is eliminating the :197 and
>>there for causing my subsequent WHERE to fail.
>>So, it might be an adonet issue and not a sql server issue - i'm not sure
>>but I don't believe it should be cutting off the 197.
>>Thanks for your circumvention.
>>T
>>
>>"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>>news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>>
>>Tina,
>>Try:
>>SELECT...
>>FROM ...
>>WHERE COL >= '20052007' AND COL <'20052108'
>>HTH
>>Jerry
>>"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
>>news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl...
>>
>>I'm writing a program that is dynamically writing SQL for SQLServer. I'm
>>formulating an UPDATE and writing WHERE clauses for the columns in a
>>table. (I'm in a .net aspx.vb program using a data adapter) In a
>>specific record a datetime field has '7/20/2005 08:07:58 AM'
>>my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
>>causes the update to fail apparently because it is a mismatch. BOL says
>>"To search for an exact match on both date and time, use an equal sign
>>(=). Microsoft SQL Server returns date and time values exactly matching
>>the month, day, and year, and at the precise time of 12:00:00:000 A.M.
>>(default)."
>>So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
>>fail too. and so does 12:00:00:000 AM
>>How is this supposed to be done?
>>Thanks,
>>T
>>
>>
>>
>>
>>
--020505030701070500060209
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>OK, this is not a bug but rather a design choice (at least that's
the case with SQLEM, I assume the designers of the SQLClient Data
Adapter made the same choice).<br>
<br>
Think about this - if you store an inexact floating point number in a
database (eg. 73.87987409000010020000263...) and want to display it on
screen to a user, would you display "</tt><tt>73.87987409000010020000263</tt><tt>"
or would you make a design call to only display what you think the user
needs to know unless they ask otherwise? That is, would you perhaps
display "</tt><tt>73.88</tt><tt>" instead, even though that's not the
actually value stored in the DB? Seems like a reasonable decision to
make. Well, the designers of SQLEM decided not to display the millisec
info when you ask for a datetime column to be shown on screen with the
"Open Table" context menu. I don't believe this is a bug but rather
what they chose to display on screen when presenting a datetime column
(the Open Table feature, after all, is just supposed to be a quick
"let's see what the data looks like" type feature IMO - for any serious
data interrogation Query Analyser is the way to go). SQLEM has been
around for 5 years + the beta testing phase; I think a simple bug like
that would have been picked up since then.<br>
<br>
I think it would be safe to assume that Microsoft made the same call
with the .NET SQLClient Data Adapter, or whichever component that is
layered on top of it that's responsible for dropping the millisec
info. I'd be surprised if you couldn't specify, in some property
attribute of some component in your VS project (like the data set or
binding source or display grid or one of those), a display format for
the datetime data such that it displays the millisec info when you ask
for it. But that might be a question better asked in the .NET
newsgroups.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tina wrote:
<blockquote cite="midOoA4vnc1FHA.2212@.TK2MSFTNGP15.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2769" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Mike,</font></div>
<div><font face="Arial" size="2">I'm not sure what I'm trying to say
is getting through clear to you. There is a date in my sql server
table that is '07/20/2005 08:07:58:197 AM'</font></div>
<div> </div>
<div><font face="Arial" size="2">If I select it using Query Analyzer
the :197 comes through. If I select it through E.M. it does NOT come
through. If I select it using a .net Data Adapter it does NOT come
through. I don't think that E.M. and the .Net data Adapters should be
chopping off data.</font></div>
<div> </div>
<div><font face="Arial" size="2">I am wondering why such a thing
happens and what could be done to get around what looks to be a bug.</font></div>
<div> </div>
<div><font face="Arial" size="2">T</font></div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:euPfYoR1FHA.972@.TK2MSFTNGP10.phx.gbl">news:euPfYoR1FHA.972@.TK2MSFTNGP10.phx.gbl</a>...</div>
<tt>No, if SQL Server receives a statement comparing a datetime
column with a string literal that implicitly converts to a datetime
datatype (such as "WHERE col1 = '20050720 08:07:58.197'") then it
honours the request. It doesn't truncate any characters from the
string literal (unless you're type casting into a smalldatetime instead
of a datetime because smalldatetime datatypes don't store millisec or
sec info). I assume you're not doing explicit conversions and are just
leaving it up to SQL Server to implicitly convert the string (you don't
mention this in your post).<br>
<br>
I would say that the millisec info is getting chopped off before it
hits the SQL Server. If you really want to be sure you can run SQL
Profiler and watch the statement as SQL Server receives it to see
exactly what is getting through the intermediate layers (i.e. ADO,
etc.).<br>
<br>
Also, you ought to express string literals that convert to datetime
data as "yyyymmdd hh:nn:ss.000". All the other formats (where the
month is not spelled out with alpha chars rather than numeric chars)
are ambiguous.<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tina wrote:
<blockquote cite="miduORaOJQ1FHA.2132@.TK2MSFTNGP15.phx.gbl"
type="cite">
<pre wrap="">Well that is a circumvention that would probably work but...
I discovered that the date in the database is actually 7/20/2005 8:07:58:197
AM and that ADONET somewhere in the dataadapter is eliminating the :197 and
there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not sure
but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:jspivey@.vestas-awt.com"><jspivey@.vestas-awt.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl">news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Tina,
Try:
SELECT...
FROM ...
WHERE COL >= '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:tinamseaburn@.nospammeexcite.com"><tinamseaburn@.nospammeexcite.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl">news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">I'm writing a program that is dynamically writing SQL for SQLServer. I'm
formulating an UPDATE and writing WHERE clauses for the columns in a
table. (I'm in a .net aspx.vb program using a data adapter) In a
specific record a datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate = '7/20/2005 08:07:58 AM' and this
causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign
(=). Microsoft SQL Server returns date and time values exactly matching
the month, day, and year, and at the precise time of 12:00:00:000 A.M.
(default)."
So I tried just putting WHERE LastUpdate = '7/20/2005' but that causes a
fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
T
</pre>
</blockquote>
<pre wrap=""> </pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--020505030701070500060209--|||This is a multi-part message in MIME format.
--=_NextPart_000_0032_01C5D628.1D525CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The precision was there in the DateTime object. Turns out I had to =manually rebind it from the datatable to the datagrid. I agree that it =was a design call albeit a bad one.
Thanks for the help!
T
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:eXw02he1FHA.2792@.tk2msftngp13.phx.gbl...
OK, this is not a bug but rather a design choice (at least that's the =case with SQLEM, I assume the designers of the SQLClient Data Adapter =made the same choice).
Think about this - if you store an inexact floating point number in a =database (eg. 73.87987409000010020000263...) and want to display it on =screen to a user, would you display "73.87987409000010020000263" or =would you make a design call to only display what you think the user =needs to know unless they ask otherwise? That is, would you perhaps =display "73.88" instead, even though that's not the actually value =stored in the DB? Seems like a reasonable decision to make. Well, the =designers of SQLEM decided not to display the millisec info when you ask =for a datetime column to be shown on screen with the "Open Table" =context menu. I don't believe this is a bug but rather what they chose =to display on screen when presenting a datetime column (the Open Table =feature, after all, is just supposed to be a quick "let's see what the =data looks like" type feature IMO - for any serious data interrogation =Query Analyser is the way to go). SQLEM has been around for 5 years + =the beta testing phase; I think a simple bug like that would have been =picked up since then.
I think it would be safe to assume that Microsoft made the same call =with the .NET SQLClient Data Adapter, or whichever component that is =layered on top of it that's responsible for dropping the millisec info. =I'd be surprised if you couldn't specify, in some property attribute of =some component in your VS project (like the data set or binding source =or display grid or one of those), a display format for the datetime data =such that it displays the millisec info when you ask for it. But that =might be a question better asked in the .NET newsgroups.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
Tina wrote: Mike,
I'm not sure what I'm trying to say is getting through clear to you. = There is a date in my sql server table that is '07/20/2005 08:07:58:197 =AM'
If I select it using Query Analyzer the :197 comes through. If I =select it through E.M. it does NOT come through. If I select it using a =.net Data Adapter it does NOT come through. I don't think that E.M. and =the .Net data Adapters should be chopping off data.
I am wondering why such a thing happens and what could be done to =get around what looks to be a bug.
T
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in =message news:euPfYoR1FHA.972@.TK2MSFTNGP10.phx.gbl...
No, if SQL Server receives a statement comparing a datetime column =with a string literal that implicitly converts to a datetime datatype =(such as "WHERE col1 =3D '20050720 08:07:58.197'") then it honours the =request. It doesn't truncate any characters from the string literal =(unless you're type casting into a smalldatetime instead of a datetime =because smalldatetime datatypes don't store millisec or sec info). I =assume you're not doing explicit conversions and are just leaving it up =to SQL Server to implicitly convert the string (you don't mention this =in your post).
I would say that the millisec info is getting chopped off before =it hits the SQL Server. If you really want to be sure you can run SQL =Profiler and watch the statement as SQL Server receives it to see =exactly what is getting through the intermediate layers (i.e. ADO, =etc.).
Also, you ought to express string literals that convert to =datetime data as "yyyymmdd hh:nn:ss.000". All the other formats (where =the month is not spelled out with alpha chars rather than numeric chars) =are ambiguous.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
Tina wrote: Well that is a circumvention that would probably work but...
I discovered that the date in the database is actually 7/20/2005 =8:07:58:197 AM and that ADONET somewhere in the dataadapter is eliminating the :197 =and there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not =sure but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:%235zU6UP1FHA.3660@.TK2MSFTNGP15.phx.gbl...
Tina,
Try:
SELECT...
FROM ...
WHERE COL >=3D '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message news:uJ%23GcRP1FHA.2072@.TK2MSFTNGP12.phx.gbl...
I'm writing a program that is dynamically writing SQL for SQLServer. = I'm formulating an UPDATE and writing WHERE clauses for the columns in a table. (I'm in a .net aspx.vb program using a data adapter) In a specific record a datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate =3D '7/20/2005 08:07:58 AM' and =this causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign (=3D). Microsoft SQL Server returns date and time values exactly =matching the month, day, and year, and at the precise time of 12:00:00:000 A.M. (default)."
So I tried just putting WHERE LastUpdate =3D '7/20/2005' but that causes =a fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
T
--=_NextPart_000_0032_01C5D628.1D525CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The precision was there in the DateTime =object. Turns out I had to manually rebind it from the datatable =to the datagrid. I agree that it was a design call albeit a bad =one.
Thanks for the help!
T
"Mike Hodgson"
--mike =hodgsonblog: http://sqlnerd.blogspot.com Tina wrote:
Mike,
I'm not sure what I'm trying to say =is getting through clear to you. There is a date in my sql server table =that is '07/20/2005 08:07:58:197 AM'
If I select it using Query Analyzer =the :197 comes through. If I select it through E.M. it does NOT come through. If I select it using a .net Data Adapter it does NOT =come through. I don't think that E.M. and the .Net data Adapters =should be chopping off data.
I am wondering why such a thing =happens and what could be done to get around what looks to be a =bug.
T
"Mike Hodgson"
--mike =hodgsonblog: http://sqlnerd.blogspot.com Tina wrote: Well that is a circumvention that would =probably work but...
I discovered that the date in the database is actually 7/20/2005 =8:07:58:197 AM and that ADONET somewhere in the dataadapter is eliminating the :197 =and there for causing my subsequent WHERE to fail.
So, it might be an adonet issue and not a sql server issue - i'm not =sure but I don't believe it should be cutting off the 197.
Thanks for your circumvention.
T
"Jerry Spivey" = wrote in message news:%235zU6UP1FHA.=3660@.TK2MSFTNGP15.phx.gbl...
Tina,
Try:
SELECT...
FROM ...
WHERE COL >=3D '20052007' AND COL <'20052108'
HTH
Jerry
"Tina" wrote in message news:uJ%23GcRP1FHA.=2072@.TK2MSFTNGP12.phx.gbl...
I'm writing a program =that is dynamically writing SQL for SQLServer. I'm formulating an UPDATE and writing WHERE clauses for the columns in a table. (I'm in a .net aspx.vb program using a data adapter) In a specific record a datetime field has '7/20/2005 08:07:58 AM'
my WHERE clause says WHERE LastUpdate =3D '7/20/2005 08:07:58 AM' and =this causes the update to fail apparently because it is a mismatch. BOL says
"To search for an exact match on both date and time, use an equal sign (=3D). Microsoft SQL Server returns date and time values exactly =matching the month, day, and year, and at the precise time of 12:00:00:000 A.M. (default)."
So I tried just putting WHERE LastUpdate =3D '7/20/2005' but that causes =a fail too. and so does 12:00:00:000 AM
How is this supposed to be done?
Thanks,
T
=
--=_NextPart_000_0032_01C5D628.1D525CA0--
No comments:
Post a Comment