DB Backup jobs

Hello,
Recently we had a hardware failure and I had to move SQLserver
databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
the Mainteance plans and jobs as well. None of the jobs run now. Even if I
kick them off, manually they sit at " Executing Job step1", for ever and it
never completes. I went ahead and deleted all the jobs, deleted all the
maintenance plans. Created a brand new maintenance plan. Still having the
same issue. Can somebody help me point to what could be causing this issue.
RK
That is not much to go on. What account is SQL Agent running under? Is
there anything in the error logs?
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:ADF8D241-013E-425E-86C2-6B6D9E0F36BF@.microsoft.com...
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
> Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
> all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
> it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this
> issue.
> --
> RK
|||SQLAgent was running as Local System and I changed it to a user(local user)
who is part of the administrator group. So is MSSQLServer service.
When I login as that user (local user) I can create files and folders in the
backup directory.
I tried right clicking the database -> all Tasks -> bcakup : this works fine.
RK
"RK73" wrote:

> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this issue.
> --
> RK
|||1) make a VERY simple sql agent job (like all it does is SELECT 1) and make
sure that simple thing works. If not, there could be a serious problem with
the install since you put the agent service account as local admin.
2) if your maintenance plans interact with ANY resource outside the server
sql is installed on, your service accounts need to be domain oriented and
have sufficient permissions to do the interaction.
3) do you get ANY log entries for the job starting/failing? add a few steps
in the job that somehow tell you that certain steps have started/finished to
see if you can isolate the problem.
TheSQLGuru
President
Indicium Resources, Inc.
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:5B0F58E6-138C-42B1-A987-E28EA2529F02@.microsoft.com...[vbcol=seagreen]
> SQLAgent was running as Local System and I changed it to a user(local
> user)
> who is part of the administrator group. So is MSSQLServer service.
> When I login as that user (local user) I can create files and folders in
> the
> backup directory.
> I tried right clicking the database -> all Tasks -> bcakup : this works
> fine.
> --
> RK
>
> "RK73" wrote:
sql

DB Backup jobs

Hello,
Recently we had a hardware failure and I had to move SQLserver
databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
the Mainteance plans and jobs as well. None of the jobs run now. Even if I
kick them off, manually they sit at " Executing Job step1", for ever and it
never completes. I went ahead and deleted all the jobs, deleted all the
maintenance plans. Created a brand new maintenance plan. Still having the
same issue. Can somebody help me point to what could be causing this issue.
--
RKThat is not much to go on. What account is SQL Agent running under? Is
there anything in the error logs?
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:ADF8D241-013E-425E-86C2-6B6D9E0F36BF@.microsoft.com...
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
> Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
> all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
> it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this
> issue.
> --
> RK|||SQLAgent was running as Local System and I changed it to a user(local user)
who is part of the administrator group. So is MSSQLServer service.
When I login as that user (local user) I can create files and folders in the
backup directory.
I tried right clicking the database -> all Tasks -> bcakup : this works fine.
--
RK
"RK73" wrote:
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this issue.
> --
> RK|||1) make a VERY simple sql agent job (like all it does is SELECT 1) and make
sure that simple thing works. If not, there could be a serious problem with
the install since you put the agent service account as local admin.
2) if your maintenance plans interact with ANY resource outside the server
sql is installed on, your service accounts need to be domain oriented and
have sufficient permissions to do the interaction.
3) do you get ANY log entries for the job starting/failing? add a few steps
in the job that somehow tell you that certain steps have started/finished to
see if you can isolate the problem.
--
TheSQLGuru
President
Indicium Resources, Inc.
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:5B0F58E6-138C-42B1-A987-E28EA2529F02@.microsoft.com...
> SQLAgent was running as Local System and I changed it to a user(local
> user)
> who is part of the administrator group. So is MSSQLServer service.
> When I login as that user (local user) I can create files and folders in
> the
> backup directory.
> I tried right clicking the database -> all Tasks -> bcakup : this works
> fine.
> --
> RK
>
> "RK73" wrote:
>> Hello,
>> Recently we had a hardware failure and I had to move SQLserver
>> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
>> Windows
>> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
>> all
>> the Mainteance plans and jobs as well. None of the jobs run now. Even if
>> I
>> kick them off, manually they sit at " Executing Job step1", for ever and
>> it
>> never completes. I went ahead and deleted all the jobs, deleted all the
>> maintenance plans. Created a brand new maintenance plan. Still having the
>> same issue. Can somebody help me point to what could be causing this
>> issue.
>> --
>> RK

DB Backup jobs

Hello,
Recently we had a hardware failure and I had to move SQLserver
databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get al
l
the Mainteance plans and jobs as well. None of the jobs run now. Even if I
kick them off, manually they sit at " Executing Job step1", for ever and it
never completes. I went ahead and deleted all the jobs, deleted all the
maintenance plans. Created a brand new maintenance plan. Still having the
same issue. Can somebody help me point to what could be causing this issue.
RKThat is not much to go on. What account is SQL Agent running under? Is
there anything in the error logs?
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:ADF8D241-013E-425E-86C2-6B6D9E0F36BF@.microsoft.com...
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
> Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
> all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
> it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this
> issue.
> --
> RK|||SQLAgent was running as Local System and I changed it to a user(local user)
who is part of the administrator group. So is MSSQLServer service.
When I login as that user (local user) I can create files and folders in the
backup directory.
I tried right clicking the database -> all Tasks -> bcakup : this works fine
.
--
RK
"RK73" wrote:

> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS Window
s
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this issue
.
> --
> RK|||1) make a VERY simple sql agent job (like all it does is SELECT 1) and make
sure that simple thing works. If not, there could be a serious problem with
the install since you put the agent service account as local admin.
2) if your maintenance plans interact with ANY resource outside the server
sql is installed on, your service accounts need to be domain oriented and
have sufficient permissions to do the interaction.
3) do you get ANY log entries for the job starting/failing? add a few steps
in the job that somehow tell you that certain steps have started/finished to
see if you can isolate the problem.
TheSQLGuru
President
Indicium Resources, Inc.
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:5B0F58E6-138C-42B1-A987-E28EA2529F02@.microsoft.com...[vbcol=seagreen]
> SQLAgent was running as Local System and I changed it to a user(local
> user)
> who is part of the administrator group. So is MSSQLServer service.
> When I login as that user (local user) I can create files and folders in
> the
> backup directory.
> I tried right clicking the database -> all Tasks -> bcakup : this works
> fine.
> --
> RK
>
> "RK73" wrote:
>

DB backup from command line ?

What ius the synthax to backup an SQLServer DB from the command line ?
ThanksUse the OSQL utility. Type:

OSQL h

at the command line to get a list of parameters for specifying a server, database, login, etc. You can use the OSQL utility to run a simple BACKUP command, or you can specify a batch file of SQL commands to perform your maintenance.sql

DB architecture

Hi there.
We are building a new mission critical application in our company, using SQL
server 2000 as the RDBMS. The new database is replacing a legacy system that
used to run in two platforms: the day to day operations (OLTP) was
maintained in a small DB2 database running on a OS/2 PC (only 1 week of
data) and the rest was moved periodically to an iseries IBM server (DB2).
all the OLTP was done on the PC, and most of the reporting was done against
the iseries DB2 database.
So far we only have one database to replace the two systems mentioned. We
are planning to either separate the data and have the reporting done in
another SQL server machine (with the same schema,and using log shipping) or
create a set of tables that would pre-process the information and would be
used by the reporting tools. When planning for theses, we created a set of
views tha are being use by our current reports (this layering protects us if
we need to change the underlying schema). The current DB is highly
normalized and I don't think would operate well for OLAP. Is the log
shipping approach recommended? Is there a better alternative? I wouldn'
like to have to maintain two different schemas.
Thank you,
Pedro."PeyoQuintero" <pedroquintero@.earthlink.net> wrote in message
news:u73cc.10784$NL4.2990@.newsread3.news.atl.earthlink.net...
> Hi there.
> We are building a new mission critical application in our company, using
SQL
> server 2000 as the RDBMS. The new database is replacing a legacy system
that
> used to run in two platforms: the day to day operations (OLTP) was
> maintained in a small DB2 database running on a OS/2 PC (only 1 week of
> data) and the rest was moved periodically to an iseries IBM server (DB2).
> all the OLTP was done on the PC, and most of the reporting was done
against
> the iseries DB2 database.
> So far we only have one database to replace the two systems mentioned. We
> are planning to either separate the data and have the reporting done in
> another SQL server machine (with the same schema,and using log shipping)
or
> create a set of tables that would pre-process the information and would be
> used by the reporting tools. When planning for theses, we created a set of
> views tha are being use by our current reports (this layering protects us
if
> we need to change the underlying schema). The current DB is highly
> normalized and I don't think would operate well for OLAP. Is the log
> shipping approach recommended? Is there a better alternative? I wouldn'
> like to have to maintain two different schemas.
> Thank you,
>
The answer, as so often with these sorts of questions, is "It depends". If
you want the maximum performance then on the OLTP database have a highly
normalized schema and few indexes. Normalized data means the integrity of
your data is easily maintained.
On the OLAP database, if no-one is updating the data directly, then you
already know the data is correct. Normalization is no longer required, and
the speed of your queries can be improved by creating tables that reflect
your views. No messy or slow joins for SQL to deal with. Bung in your
indexes to speed the filtering and grouping of your data. Include
calculated and aggragated data directly in your tables.
To maintain these different schemas create DTS jobs to transform and move
your data from one database to the other.
Of course, the downside of this approach, as you've identified, is
maintaining two schemas, but the OLAP database (apart from the automated DTS
jobs) is a read-only database, and therefore should not need that much
maintaining once up and running. Also, log shipping will typically have
less latency, but in your old model you imply that there was a weekly
upload, so that would not be an issue.
Log Shipping works only if both databases have the same schema. It is much
simpler than DTS, but less flexible as well.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004

DAYS 360 Function

Hi,

Does anyone has the DAYS360 excel formula in a function in sqlserver ? I did this one

FUNCTION dbo.fnDays360_EXCEL
(
@.startDate DateTime,
@.endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (
(CASE
WHEN Day(@.endDate)=31 THEN 30
ELSE Day(@.endDate)
END) -
(CASE
WHEN Day(@.startDate)=31 THEN 30
ELSE Day(@.startDate)
END)
+ ((DatePart(m, @.endDate) + (DatePart(yyyy, @.endDate) * 12))
-(DatePart(m, @.startDate) + (DatePart(yyyy, @.startDate) * 12))) * 30)
END

But there is a bug, if the end date is bigger then february, february must have 30 days and not 28 or 29...

Does anyone has the solution ?

Thanks

Hi,

Do you mean 30/360? HEre's some C# code that was tested thouroughly, you should be able to get the idea. If you meant Act/360 get back to me.

Good luck,

John

double YearFrac(DateTime dtStartDate, DateTime dtEndDate, int iDaycount)

{

/* According to Excel:

Basis Day count basis

0 or omitted US (NASD) 30/360

1 Actual/actual

2 Actual/360

3 Actual/365

4 European 30/360

7 Bus/252

*/

switch( iDaycount )

{

case 0: // 30/360 (ISDA)

{

int d1, m1, y1, d2, m2, y2;

d1 = dtStartDate.Day;

m1 = dtStartDate.Month;

y1 = dtStartDate.Year;

d2 = dtEndDate.Day;

m2 = dtEndDate.Month;

y2 = dtEndDate.Year;

// ISDA rules

if (d1 == 31) d1 = 30;

if (d2 == 31 && d1 == 30) d2 = 30;

return (360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)) / 360e0;

}

|||

Use the following code...

Code Snippet

Create Function dbo.Days360

(

@.StartDate Datetime,

@.EndDate Datetime

)

Returns Int

as

Begin

Declare

@.d1 int, @.d2 int,

@.m1 int, @.m2 int,

@.y1 int, @.y2 int;

Select @.d1 = Day(@.StartDate), @.m1 = Month(@.StartDate), @.y1 = Year(@.StartDate),

@.d2 = Day(@.EndDate), @.m2 = Month(@.EndDate), @.y2 = Year(@.EndDate)

If (day(@.StartDate) = 1) And (month(@.StartDate) = 3)

Select @.d1 = 30

If (@.d2 = 31) And (@.d1 = 30)-- Then

Select @.d2 = 30

Return ((@.y2 - @.Y1) * 360) + ((@.m2 - @.m1) * 30) + (@.d2 - @.d1)

End

go

Select dbo.Days360('1/20/2007', '2/3/2007')

|||

Hi,

There is a problem to this case Select dbo.Days360('1/31/2007', '4/15/2007') it returns 74 instead of 75

Thanks

|||

Hi

There is a problem to this case Select dbo.Days360('1/31/2007', '4/15/2007') it returns 74 instead of 75

And for this one also..

Select dbo.Days360('2/28/2007', '3/31/2007') that must return 30

For the C# code...is just this last error

Day Light Saving Time Change

According to Microsoft, Day Light saving time changes do not impact SQL
Server, but I want to prove that with some tests. (
http://support.microsoft.com/gp/cp_dst)
I tried using DATEADD and DATEDIFF with time period that covered the spring
forward time for both 4/1/2007 as well as 3/11/2007, they both returned the
wrong result
The following queries return the same time
select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
2007-04-01 02:01:00.000
Is there any other way to verify that DST change has no impact?
Thanks
SQL Server does not ever adjust times for DST changes.
DST events are only relevant for functions that retrieve local system time
(e.g. GETDATE(), CURRENT_TIMESTAMP).
If you are storing data and expect it to follow DST rules, then that is your
responsibility with the new rules, just as it has always been your
responsibility with the old rules.
"Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
news:uCdpbU6SHHA.3996@.TK2MSFTNGP04.phx.gbl...
> According to Microsoft, Day Light saving time changes do not impact SQL
> Server, but I want to prove that with some tests. (
> http://support.microsoft.com/gp/cp_dst)
> I tried using DATEADD and DATEDIFF with time period that covered the
> spring forward time for both 4/1/2007 as well as 3/11/2007, they both
> returned the wrong result
> The following queries return the same time
> select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
> select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
> 2007-04-01 02:01:00.000
> Is there any other way to verify that DST change has no impact?
> Thanks
>

Day Light Saving Time Change

According to Microsoft, Day Light saving time changes do not impact SQL
Server, but I want to prove that with some tests. (
http://support.microsoft.com/gp/cp_dst)
I tried using DATEADD and DATEDIFF with time period that covered the spring
forward time for both 4/1/2007 as well as 3/11/2007, they both returned the
wrong result
The following queries return the same time
select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
2007-04-01 02:01:00.000
Is there any other way to verify that DST change has no impact?
ThanksI found this in MS site
http://support.microsoft.com/kb/931975/en-us
According to it, dateadd or other date functions are not timezone sensitive,
so I don't see any way to test this without changing the clock on the
server.
Any ideas?
"Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
news:uCdpbU6SHHA.3996@.TK2MSFTNGP04.phx.gbl...
> According to Microsoft, Day Light saving time changes do not impact SQL
> Server, but I want to prove that with some tests. (
> http://support.microsoft.com/gp/cp_dst)
> I tried using DATEADD and DATEDIFF with time period that covered the
> spring forward time for both 4/1/2007 as well as 3/11/2007, they both
> returned the wrong result
> The following queries return the same time
> select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
> select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
> 2007-04-01 02:01:00.000
> Is there any other way to verify that DST change has no impact?
> Thanks
>|||SQL Server does not ever adjust times for DST changes.
DST events are only relevant for functions that retrieve local system time
(e.g. GETDATE(), CURRENT_TIMESTAMP).
If you are storing data and expect it to follow DST rules, then that is your
responsibility with the new rules, just as it has always been your
responsibility with the old rules.
"Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
news:uCdpbU6SHHA.3996@.TK2MSFTNGP04.phx.gbl...
> According to Microsoft, Day Light saving time changes do not impact SQL
> Server, but I want to prove that with some tests. (
> http://support.microsoft.com/gp/cp_dst)
> I tried using DATEADD and DATEDIFF with time period that covered the
> spring forward time for both 4/1/2007 as well as 3/11/2007, they both
> returned the wrong result
> The following queries return the same time
> select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
> select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
> 2007-04-01 02:01:00.000
> Is there any other way to verify that DST change has no impact?
> Thanks
>|||> so I don't see any way to test this without changing the clock on the
> server.
Yes, changing the server time is the only way to test. As Aaron mentioned,
SQL Server gets current time from Windows.
Hope this helps.
Dan Guzman
SQL Server MVP
"Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
news:eICC5j6SHHA.5100@.TK2MSFTNGP06.phx.gbl...
>I found this in MS site
> http://support.microsoft.com/kb/931975/en-us
> According to it, dateadd or other date functions are not timezone
> sensitive, so I don't see any way to test this without changing the clock
> on the server.
> Any ideas?
>
> "Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
> news:uCdpbU6SHHA.3996@.TK2MSFTNGP04.phx.gbl...
>sql

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