DateTime in a WHERE clause

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,
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...
>|||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:19
7
>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...
>
>
>|||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 Ada
pter it does NOT come through. I don't think that E.M. and the ..Net data A
dapters 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:euP
fYoR1FHA.972@.TK2MSFTNGP10.phx.gbl...
No, if SQL Server receives a statement comparing a datetime column with a st
ring 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 t
runcate any characters from the string literal (unless you're type casting i
nto a smalldatetime instead of a datetime because smalldatetime datatypes do
n't store millisec or sec info). I assume you're not doing explicit convers
ions and are just leaving it up to SQL Server to implicitly convert the stri
ng (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 wat
ch the statement as SQL Server receives it to see exactly what is getting th
rough 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 spel
led 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|||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:
[vbcol=seagreen]
> 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:
>|||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:eXw
02he1FHA.2792@.tk2msftngp13.phx.gbl...
OK, this is not a bug but rather a design choice (at least that's the case w
ith SQLEM, I assume the designers of the SQLClient Data Adapter made the sam
e choice).
Think about this - if you store an inexact floating point number in a databa
se (eg. 73.87987409000010020000263...) and want to display it on screen to a
user, would you display "73.87987409000010020000263" or would you make a de
sign 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 tho
ugh 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 wha
t they chose to display on screen when presenting a datetime column (the Ope
n Table feature, after all, is just supposed to be a quick "let's see what t
he data looks like" type feature IMO - for any serious data interrogation Qu
ery Analyser is the way to go). SQLEM has been around for 5 years + the bet
a testing phase; I think a simple bug like that would have been picked up si
nce then.
I think it would be safe to assume that Microsoft made the same call with th
e .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 you
r 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 mil
lisec info when you ask for it. But that might be a question better asked i
n 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 i
s 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 Ad
apter it does NOT come through. I don't think that E.M. and the .Net data A
dapters 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:euP
fYoR1FHA.972@.TK2MSFTNGP10.phx.gbl...
No, if SQL Server receives a statement comparing a datetime column with a st
ring 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 t
runcate any characters from the string literal (unless you're type casting i
nto a smalldatetime instead of a datetime because smalldatetime datatypes do
n't store millisec or sec info). I assume you're not doing explicit convers
ions and are just leaving it up to SQL Server to implicitly convert the stri
ng (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 wat
ch the statement as SQL Server receives it to see exactly what is getting th
rough 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 spel
led 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