dayly table update

hello,
i must dayly update a table in my database with the values of a CSV file
(~300000 entries)
example of the tabel (artNr ,productname ,price )
000001 monitor 234,66
000003 pc 699,44
....
245433 router 126,33
Now dayly the table-content is deleted and the csv-file is imported
Is it possible a better way - to update only the modified values and insert
the new.
How can this be done?
thanksOne recommendation could be
1. Create a staging table called get_bcp_h_daily_csv
2. Truncate the table
3. DTS the csv file into staging table
4. Write the first entry to a surrogate table called ot_su_daily_csv as in
a) below.
5. Write a sProc that incrementally loads what's in the surrogate table into
a lookup table called ot_lu_daily_csv for your database as in b) below:
6. Schedule a job to run this DTS Each day
7. Sorted
a)
INSERT INTO ot_su_daily_csv (ColName1, ColName2)
SELECT ColName1, ColName2
FROM get_bcp_h_daily_csv BCP
WHERE NOT EXISTS ( SELECT * FROM ot_su_daily_csv SURR
WHERE SURR.Col1= BCP.Col1 )
b.)
INSERT INTO ot_lu_daily_csv
(Col1, Col2)
SELECT Col1, Col2
FROM ot_su_daily_csv SURR(nolock)
ORDER BY Col1|||thanks for the recommendation - it works well if only each day new values in
the csv-file are attached.
But in my csv file some colums of the articles are changed - like in the
example
example: - day1
000001 monitor 234,66
000003 pc 699,44
the next day - day 2
000001 monitor 230,03 (price is modified...)
000003 pc-3,4GHz 699,44 (product description is modified)
245433 router 126,33 -> ok will be detected and updated
....
how to make a correct update in this situation ...
thanks
Xavier|||On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:

>thanks for the recommendation - it works well if only each day new values i
n
>the csv-file are attached.
>But in my csv file some colums of the articles are changed - like in the
>example
>example: - day1
>000001 monitor 234,66
>000003 pc 699,44
>the next day - day 2
>000001 monitor 230,03 (price is modified...)
>000003 pc-3,4GHz 699,44 (product description is modified)
>245433 router 126,33 -> ok will be detected and updated
>....
>how to make a correct update in this situation ...
>thanks
>Xavier
Hi Xavier,
Load the new data in a staging table. Then run a procedure that updates
existing data and adds new data, as follows:
UPDATE t
SET Descr = s.Descr,
Price = s.Price,
.. (other columns)
FROM TheTable AS t
INNER JOIN StagingTable AS s
ON s.KeyColumn = theTable.keyColumn
WHERE t.Descr <> s.Descr
OR t.Price <> s.Price
OR ... (other columns)
INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
SELECT KeyColumn, Descr, Price, ... (other columns)
FROM Stagins AS s
WHERE NOT EXISTS
(SELECT *
FROM TheTable AS t
WHERE t.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks,
Xavier
"Hugo Kornelis" wrote:

> On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:
>
> Hi Xavier,
> Load the new data in a staging table. Then run a procedure that updates
> existing data and adds new data, as follows:
> UPDATE t
> SET Descr = s.Descr,
> Price = s.Price,
> ... (other columns)
> FROM TheTable AS t
> INNER JOIN StagingTable AS s
> ON s.KeyColumn = theTable.keyColumn
> WHERE t.Descr <> s.Descr
> OR t.Price <> s.Price
> OR ... (other columns)
> INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
> SELECT KeyColumn, Descr, Price, ... (other columns)
> FROM Stagins AS s
> WHERE NOT EXISTS
> (SELECT *
> FROM TheTable AS t
> WHERE t.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Daylight Savings Time error SQL SERVER 2000

Hi all.
I used tzedit to update the daylight savings time setting on my windows 2000
server.. Now, when I run any of my reports, I get this error:
"An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name:
date "
Does anyone have or heard of a solution?Maybe there's a patch for sql 2000, i fixed my win 2003 server and didn't
encounter this issue with my SQL 2005.
"jereviscious" <here@.there.com> wrote in message
news:eEmN81vaHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Hi all.
> I used tzedit to update the daylight savings time setting on my windows
> 2000 server.. Now, when I run any of my reports, I get this error:
> "An internal error occurred on the report server. See the error log for
> more details. (rsInternalError) Get Online Help
> Specified argument was out of the range of valid values. Parameter name:
> date "
> Does anyone have or heard of a solution?
>|||Since Windows 2000 server isn't officially supported any more, microsoft
didn't release a patch. They did, however, issue this knowledge base
article, which I followed..
http://support.microsoft.com/kb/914387
and everything else appears to be working fine. All of my servers are
reporting the correct time and date. However, SQL SERVER is giving me this
problem, and I have no idea how to go about fixing it...
"Julien Bonnier" <julien@.m0851.com> wrote in message
news:OM3DXKwaHHA.3584@.TK2MSFTNGP02.phx.gbl...
> Maybe there's a patch for sql 2000, i fixed my win 2003 server and didn't
> encounter this issue with my SQL 2005.
>
> "jereviscious" <here@.there.com> wrote in message
> news:eEmN81vaHHA.4396@.TK2MSFTNGP06.phx.gbl...
>> Hi all.
>> I used tzedit to update the daylight savings time setting on my windows
>> 2000 server.. Now, when I run any of my reports, I get this error:
>> "An internal error occurred on the report server. See the error log for
>> more details. (rsInternalError) Get Online Help
>> Specified argument was out of the range of valid values. Parameter name:
>> date "
>> Does anyone have or heard of a solution?
>

DateTime Update Statement

I've looked through books online and can't understand why the below statement doesn't work:

update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'

The error returned is:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.

Thanks,

BrentOriginally posted by baolive
I've looked through books online and can't understand why the below statement doesn't work:

update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'

The error returned is:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.

Thanks,

Brent

What's the name of Month 27?|||Cut and paste this:

(Do code tags work here?)

USE Northwind
GO
CREATE TABLE tblregionalmarketrate (effectivedate datetime)
GO
INSERT INTO tblregionalmarketrate (effectivedate)
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate()
GO
SELECT * FROM tblregionalmarketrate
GO

UPDATE tblregionalmarketrate
SET effectivedate = '2003-01-27 00:00:00.000'
WHERE effectivedate > '2003-01-01 00:00:00.000'
GO

SELECT * FROM tblregionalmarketrate
GO

DROP TABLE tblregionalmarketrate
GO|||Thanks, see told you it would be simple, just had the month and day backwards. Could have sworn I tried it with the correct format before.

datetime update

I would like to change all rows in a table that have the date range between
June 1, 2005 to June 30, 2005 to the month of May. I haven't been able to
figure out the SET code that would update the rows to the the month of May
instead of June. The day and year have to remain the same. Any suggestions?
Thanking you in advance.
SherryUPDATE YourTable
SET DatCol = (CASE MONTH(DATCOL) WHEN 6 THEN DATEADD(mm,-1,DATCOL) ELSE
DATCOL END)
WHERE Datcol between '20050601' AND 20050630
HTH, Jens Suessmeyer.
"Sherry" <Sherry@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2233DF06-C29A-4DDD-A4DF-993C9AAEC42A@.microsoft.com...
>I would like to change all rows in a table that have the date range between
> June 1, 2005 to June 30, 2005 to the month of May. I haven't been able
> to
> figure out the SET code that would update the rows to the the month of May
> instead of June. The day and year have to remain the same. Any
> suggestions?
> Thanking you in advance.
> Sherry|||Thank so much. Worked perfectly.
"Jens Sü?meyer" wrote:

> UPDATE YourTable
> SET DatCol = (CASE MONTH(DATCOL) WHEN 6 THEN DATEADD(mm,-1,DATCOL) ELSE
> DATCOL END)
> WHERE Datcol between '20050601' AND 20050630
> HTH, Jens Suessmeyer.
> "Sherry" <Sherry@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2233DF06-C29A-4DDD-A4DF-993C9AAEC42A@.microsoft.com...
>
>sql

DateTime question

I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
--
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
--
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>sql

DateTime question

I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as
a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conver
sion) and thereby
exposes the internals of the product. And that happens to be different from
some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>

DateTime question

I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)
What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
|||Why not just take the c# datetime and convert to SqlDateTime and store that?
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
sql

DateTime problem

I need to UPDATE DateTime in database. But input paramter (@.DatumPozadovany) is not in Default format 'mon dd yyyy hh:miAM', but in Europian one 'dd mon yyyy HH:mi'.

This code don't work, becuse it converts standart input into unstandart output. I need the oposite of it.

UPDATE DoslaObjednavka SET DatumPozadovany = CONVERT(datetime, @.DatumPozadovany, 13), Oznaceni = @.Oznaceni WHERE (Id = @.Id)

Please, help.

SELECT CONVERT(DATETIME, GETDATE(),109)

--returns 2007-02-12 21:09:56.970

SELECT CONVERT(nvarchar(26), GETDATE(),109)

--returns Feb 12 2007 9:09:56:970PM

From the SQL Server 2005 Books Online topic
CAST and CONVERT (Transact-SQL)
"In the following table, the two columns on the left represent the style values for converting datetime or smalldatetime data to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).

|||Interestingly, you should try without the convert. If you let SQL do the conversion implicitly, it may well recognise the format you're using anyway. If you specify a format, it will have to match it exactly.

Rob|||Thaks everyone for reply. Error was at very different place.
In ASP.NET i didn't specify parametr datatype. It expected some strange datetime format and didn't work. Now it works fine.

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