DateTime.Now expression expected problem

Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column.

Using the default insert created, I have the following code:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Profile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now)

The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Dim pd as partyDetails.partyDetailsDataTable
pd = da.GetPartyDetailsByID(Profile.partyid)
da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid)

Have I an error in my Insert section?

Thanks for any help,

Mark

Look at what the functions da.Insert and ds.Update are expecting as their arguments.

Maybe daInsert is expecting the date as a string instead of a DateTime object

DateTime using DateDiff

Hello everyone.

Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).

To find @.PurchaseTotal for the year, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
 DateDiff(yyyy, DateOf, @.IntervalDate) = @.Interval

To find @.PurchaseTotal for a month, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
 DateDiff(mm, DateOf, @.IntervalDate) = @.Interval

To find @.PurchaseTotal for all the records, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses

I've tried the following code but I get an error.

DateDiff(@.DateParameter, DateOf, @.IntervalDate) = @.Interval

Error says something like "incorrect parameter 1 for DateDiff."

It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.

Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?

Thank you ahead for any direction you can give.

AlecGratulations for choosingthe worst possible approach.

::WHERE DateDiff(mm, DateOf, @.IntervalDate) = @.Interval

Means, in SQL Server language: DO NOT USE AN INDEX.

Why do you not go the easy way?

::WHERE DateOf BETWEEN @.StartDate and @.EndDate

which is WAY less processing for SQL Server, allows it to use an index and in general is faster?

Datetime problems

Hallo,

I have two different problems with datetime columns and MSSQL:

1)
Connecting via PHP 4.4.4 (Linux/Apache/freetds/mssql_connect) to MSSQL I receive a wrong month when selecting a datetime column: 00 is January and 11 December. E.g.:
select getdate()
2007-00-04 19:14:48

Selecting "convert(varchar(30), getdate(), 120)" returns the correct date:
2007-01-04 19:14:48
But I need that getdate() or selecting datetime columns without convert works correct, too.

2)
Connecting via PHP 4.4.4 (Windows Server 2003/ISAPI/MS-IIS 6.0/mssql_connect) to MSSQL and updating a datetime column it interchanges month and day, e.g.
update [mytable] set mydatetimecolumn="2007-01-04 19:14:48"
will set the datetime column to "2007-04-01 19:14:48".
I solved the problem with a "set dateformat ymd" before updating but I would prefer a permanent solution without the set command.

Thanks for any help1) This looks like a driver problem to me. Since converting the date to a string on SQL Server gives you the right date, nothing is wrong there. See if there are any know problems with the drivers and make sure you have the latest version.

2) De format in which the dates are handled on SQL server is determined in your server settings. Sending a string and hoping it will be alright is never a good idea (moving the databases to another server can get you in a lot of trouble that way). Always tell SQL Server in which format you are supplying the date by using the convert-function:
UPDATE[mytable]
SET mydatetimecolumn= CONVERT(DATETIME, '2007-01-04 19:14:48', 120)|||Thank you very much for your reply, Lexiflex.

2)
I need it in generic scripts where I don't know which column is a datetime field. And I don't really want to detect which type each column has before updating.
I thought that this format (ODBC canonical) cannot be misunderstood and that every SQL DBMS would interprete it correctly.
The strange thing is that I receive datetime columns in ODBC canonical ("YYYY-MM-DD HH:MM:SS") when selecting them.
Is there any datetime format which MSSQL can interprete without a convert command ? I read somewhere that "YYYYMMDD HH:MM:SS" could be. But I didn't found it in the CAST/CONVERT table. And I don't know how to convince MSSQL to return the datetime columns in this format by default.

Any suggestions ?|||The format "YYYYMMDD HH:MM: SS" seems to work and I sometimes use it when I need to use a date quickly in a development area.

I don't know how to convince MSSQL to return the datetime columns in this format by default.
You have to understand that sending a date is different from receiving one. When SQL Server returns a date it does so in a, to me, unknown way and it is the client application that formats it before presenting it to you (e.g. you can tell Query Analyzer how to show dates). So if you want to present it in a certain format you have to look a the settings of your client application.|||1)
The problem is still unsolved.
Does anybody have the same problem?
The problem seems to come from the PHP mssql support.
(I have some bigger problems with the odbc driver, too. So I cannot use it.)

Using odbc- or mssql- connect return different results
when selecting getdate() although both should use freetds i think:
PHP using odbc_connect (correct)
2007-02-05 16:25:25
PHP using mssql_connect (wrong)
2007-01-05 16:25:25

TSQL (freetds) tells me the correct date, too:
1> select getdate()
2> go
2007-02-05 16:25:25

Here more details of the system:
-----------
Apache/1.3.33 (Debian GNU/Linux)
PHP/4.4.4
mssql Library version 7.0
mssql.compatability_mode off
mssql.datetimeconvert off
ODBC library unixODBC
freetds v0.62.4 (TDS version: 8.0, unixodbc: yes)

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 1)

PLEASE HELP!|||1)
Finally we solved the problem with compiling FreeTDS using --enable-msdblib.

See also:
http://bugs.php.net/bug.php?id=22060
http://bugs.php.net/bug.php?id=32022

Datetime only time needed

Wel maybe someone could help me.
I'm working with an excisting database using MSSQL, where are 2 T_datetime
fields.
If i display these 2 columns i see the date time format as follow:
Aug 5 2004 6:03PM
The problem is that i only need de time format like 6:03PM or better if
possible 18:03.
Could anybody help mee with these 2 problems.See function CONVERT in BOL. It is better to do the formatting in your clien
t
app, reporting tool, or programming language.
select left(right(convert(varchar(25), getdate(), 100), 7), 5)
AMB
"Smarteye" wrote:

> Wel maybe someone could help me.
> I'm working with an excisting database using MSSQL, where are 2 T_datetime
> fields.
> If i display these 2 columns i see the date time format as follow:
> Aug 5 2004 6:03PM
> The problem is that i only need de time format like 6:03PM or better if
> possible 18:03.
> Could anybody help mee with these 2 problems.|||I cannot change the excisting Database,
If i use this code i see no output in Php.
does any know
"Alejandro Mesa" wrote:
> See function CONVERT in BOL. It is better to do the formatting in your cli
ent
> app, reporting tool, or programming language.
>
> select left(right(convert(varchar(25), getdate(), 100), 7), 5)
>
> AMB
>
> "Smarteye" wrote:
>|||> I cannot change the excisting Database,
I did not say to change the db.

> If i use this code i see no output in Php.
> does any know
>
Which code?
AMB
"Smarteye" wrote:
> I cannot change the excisting Database,
> If i use this code i see no output in Php.
> does any know
> "Alejandro Mesa" wrote:
>|||What do you mean, if I display these 2 columns? Can you do a select in QA
and post the results?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
news:4EDDC42C-6CCE-4685-B84E-936C177C2E6A@.microsoft.com...
> Wel maybe someone could help me.
> I'm working with an excisting database using MSSQL, where are 2 T_datetime
> fields.
> If i display these 2 columns i see the date time format as follow:
> Aug 5 2004 6:03PM
> The problem is that i only need de time format like 6:03PM or better if
> possible 18:03.
> Could anybody help mee with these 2 problems.|||$query = " select left(right(convert(varchar(25), PunchIn(), 100), 7), 5)";
//punch in is columname
this is my query what was suggest when i use this i have no output.
"Louis Davidson" wrote:

> What do you mean, if I display these 2 columns? Can you do a select in QA
> and post the results?
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
> news:4EDDC42C-6CCE-4685-B84E-936C177C2E6A@.microsoft.com...
>
>|||What is punchIn() or punchLn(). This is not valid SQL. Can you print what
is in your $query variable and post?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
news:40399FA1-87F5-4D8A-97B9-F1F655F814B7@.microsoft.com...
> $query = " select left(right(convert(varchar(25), PunchIn(), 100), 7),
> 5)";
> //punch in is columname
> this is my query what was suggest when i use this i have no output.
> "Louis Davidson" wrote:
>|||Well it's working,
for one field if i do :
$query = " SELECT left(right(convert(varchar(25), PunchIn,100), 7), 7) FROM
X_PunchIn"; //punch in is columname
it wil display the time good.
but i need to display 2 columns
so i've made a 2nd query :
$query2 = "SELECT left(right(convert(varchar(25), PunchOut,100), 7), 5) FROM
X_PunchIn"; //punch in is columname
but mssql could only display 1 query so i've a problem .
Could any one solve this problem? and the problem AM/PM to 24hours
"Louis Davidson" wrote:

> What is punchIn() or punchLn(). This is not valid SQL. Can you print wha
t
> is in your $query variable and post?
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
> news:40399FA1-87F5-4D8A-97B9-F1F655F814B7@.microsoft.com...
>
>

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,
T
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
>
|||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: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...
>
>
>
|||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 = '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
|||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: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
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 = '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

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...
>> 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" wrote in message news:euPfYoR1FHA.972@.T=K2MSFTNGP10.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 =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" 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 =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" wrote in message news:euPfYoR1FHA.972@.T=K2MSFTNGP10.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 =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--

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

DATETIME Default getDate() query

Hi,
I have created a datagrid within an ASP.NET page that links to an SQL
table - this works just fine. One of the columns retrieves the date which a
particular row was populated. However, the format
which the date is returned is:
19.12.2004 13:03:25
I only want to display the date that the row was populated, not the exact
time as well. When I created the table I used the following code (I've cut
out the rest of the fields):
CREATE TABLE Users
(
Paul,
You can use this as your default:
Default dateadd(d,0,datediff(d,0,getdate()))
It works by finding the number of day boundaries between day 0 (January
1, 1900) and now, and adds that many days back to day 0.
Steve Kass
Drew University
Paul Evans wrote:

>Hi,
>I have created a datagrid within an ASP.NET page that links to an SQL
>table - this works just fine. One of the columns retrieves the date which a
>particular row was populated. However, the format
>which the date is returned is:
>19.12.2004 13:03:25
>I only want to display the date that the row was populated, not the exact
>time as well. When I created the table I used the following code (I've cut
>out the rest of the fields):
>CREATE TABLE Users
>(
> .
> .
> .
> .
> u_entrydate DATETIME Default getDate()
>)
>
>Is this to do with the DATETIME Default getDate() code for creating the
>column? Is there something else I could use to get just the date only, with
>out the time of day the row was populated?
>I'm using a microsoft SQL server.
>Thanks for your time
>Paul Evans
>P.S. or is it a problem with my ASP.NET code?
>
>