3.33 millisecond accuracy in datetime

I'm still not clear on the technical reason why datetime values are
accurate to 3.33 milliseconds. I think I understand that it has to do
with the fact that what is stored for the time value is the number of
ticks from time 0 of that day which is stored as the number of days
from day 0 (1/1/1900 i believe). What I can't seem to get is why a cpu
tick is equal to 3.33 milliseconds. Is this an electrical engineering
related issue based on voltage/frequency type things (i'm obviously not
an electrical engineer) and how the tick is "fired" or some other type
of software based limitation. Any info that could help clarify for me
is appreciated.I don't think it has anything to do with CPU clock ticks. A datetime
datatype is stored as two 32-bit integers: 1 for the date part and 1 for
the time part. Ignoring the date part and just looking at the time
part, you can see that there are a finite number of possible values for
the time.
There ought to be 2^32 different possible values, which equates to an
accuracy of about 1/49,710 of a second (that is,
1/((2^32)/(24h*60m*60s))), but for some reason the Microsoft developers
decided to limit it to 1/300 of a second. I don't know why but I'm
guessing it's convenient for calculations etc. Whatever the reason,
it's not about CPU clock ticks but rather how the data is physically
stored. This is the reason, I believe, that smalldatetime is only
accurate to the minute, because with only a 16-bit integer to store the
time information it can only store a maximum of 65536 different time
values (which equates to about 45 intervals per minute and so making it
accurate to a minute seems convenient).
*mike hodgson*
http://sqlnerd.blogspot.com
lairdnet@.gmail.com wrote:

>I'm still not clear on the technical reason why datetime values are
>accurate to 3.33 milliseconds. I think I understand that it has to do
>with the fact that what is stored for the time value is the number of
>ticks from time 0 of that day which is stored as the number of days
>from day 0 (1/1/1900 i believe). What I can't seem to get is why a cpu
>tick is equal to 3.33 milliseconds. Is this an electrical engineering
>related issue based on voltage/frequency type things (i'm obviously not
>an electrical engineer) and how the tick is "fired" or some other type
>of software based limitation. Any info that could help clarify for me
>is appreciated.
>
>

3.33 millisecond accuracy in datetime

I'm still not clear on the technical reason why datetime values are
accurate to 3.33 milliseconds. I think I understand that it has to do
with the fact that what is stored for the time value is the number of
ticks from time 0 of that day which is stored as the number of days
from day 0 (1/1/1900 i believe). What I can't seem to get is why a cpu
tick is equal to 3.33 milliseconds. Is this an electrical engineering
related issue based on voltage/frequency type things (i'm obviously not
an electrical engineer) and how the tick is "fired" or some other type
of software based limitation. Any info that could help clarify for me
is appreciated.This is a multi-part message in MIME format.
--040809030603070702040001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I don't think it has anything to do with CPU clock ticks. A datetime
datatype is stored as two 32-bit integers: 1 for the date part and 1 for
the time part. Ignoring the date part and just looking at the time
part, you can see that there are a finite number of possible values for
the time.
There ought to be 2^32 different possible values, which equates to an
accuracy of about 1/49,710 of a second (that is,
1/((2^32)/(24h*60m*60s))), but for some reason the Microsoft developers
decided to limit it to 1/300 of a second. I don't know why but I'm
guessing it's convenient for calculations etc. Whatever the reason,
it's not about CPU clock ticks but rather how the data is physically
stored. This is the reason, I believe, that smalldatetime is only
accurate to the minute, because with only a 16-bit integer to store the
time information it can only store a maximum of 65536 different time
values (which equates to about 45 intervals per minute and so making it
accurate to a minute seems convenient).
--
*mike hodgson*
http://sqlnerd.blogspot.com
lairdnet@.gmail.com wrote:
>I'm still not clear on the technical reason why datetime values are
>accurate to 3.33 milliseconds. I think I understand that it has to do
>with the fact that what is stored for the time value is the number of
>ticks from time 0 of that day which is stored as the number of days
>from day 0 (1/1/1900 i believe). What I can't seem to get is why a cpu
>tick is equal to 3.33 milliseconds. Is this an electrical engineering
>related issue based on voltage/frequency type things (i'm obviously not
>an electrical engineer) and how the tick is "fired" or some other type
>of software based limitation. Any info that could help clarify for me
>is appreciated.
>
>
--040809030603070702040001
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>I don't think it has anything to do with CPU clock ticks. A
datetime datatype is stored as two 32-bit integers: 1 for the date part
and 1 for the time part. Ignoring the date part and just looking at
the time part, you can see that there are a finite number of possible
values for the time.<br>
<br>
There ought to be 2^32 different possible values, which equates to an
accuracy of about 1/49,710 of a second (that is,
1/((2^32)/(24h*60m*60s))), but for some reason the Microsoft developers
decided to limit it to 1/300 of a second. I don't know why but I'm
guessing it's convenient for calculations etc. Whatever the reason,
it's not about CPU clock ticks but rather how the data is physically
stored. This is the reason, I believe, that smalldatetime is only
accurate to the minute, because with only a 16-bit integer to store the
time information it can only store a maximum of 65536 different time
values (which equates to about 45 intervals per minute and so making it
accurate to a minute seems convenient).</tt><br>
<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"><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>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:lairdnet@.gmail.com">lairdnet@.gmail.com</a> wrote:
<blockquote
cite="mid1146757782.740076.309510@.g10g2000cwb.googlegroups.com"
type="cite">
<pre wrap="">I'm still not clear on the technical reason why datetime values are
accurate to 3.33 milliseconds. I think I understand that it has to do
with the fact that what is stored for the time value is the number of
ticks from time 0 of that day which is stored as the number of days
from day 0 (1/1/1900 i believe). What I can't seem to get is why a cpu
tick is equal to 3.33 milliseconds. Is this an electrical engineering
related issue based on voltage/frequency type things (i'm obviously not
an electrical engineer) and how the tick is "fired" or some other type
of software based limitation. Any info that could help clarify for me
is appreciated.
</pre>
</blockquote>
</body>
</html>
--040809030603070702040001--

2ND POST: Is there a way to insert a record using another as a template?

Hi,
I am currently inserting a record using:
insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
But, the problem I have is that the table I am connecting to can either have
46 fields or 47 fields depending on its version. Is there a command that
would allow me to use an existing record as a template and only specified
the fields I want to change and then insert the new record?
Something like:
insert into TABLE(FIELD1) value ('%s') using template record where
FIELD1='9999999' ?
Thanks,
SA DevYour question is not clear
Give some sample data with expected result
Madhivanan|||Select SubQuery.* Into
NameOfYourNewTable
From
(
Select *,'Value' as NewColumnName From yourOldtable
) SubQuery
' That what you mean '
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SA Development" <nospam38925@.forme.com> schrieb im Newsbeitrag
news:ILSdnRsNE_DG4jTfRVn-2A@.valortelecom.com...
> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either
> have
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>|||Perhaps you could add a default for the additional column where it
exists and then you might not have to reference that column at all.
Alternatively, use SPs to insulate your application from different
schema versions. One of the big benefits of using SPs for data access
code is that they provide a single point of entry that can be made
backwards-compatible in later versions.
Failing that you may have to resort to Dynamic SQL. Make sure you read
up on all the implications first:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--

2ND POST: Is there a way to insert a record using another as a tem

Hi
If you specify the column list then you insert values into those columns.
Missing columns will be set to NULL (if it is allowed) or a default value.
To update existing data use the UPDATE statement, you will only change the
values for columns specified in the SET clause. More information on the
INSERT and UPDATE statements can be found in Books Online.
Using stored procedure may help to remove any issues with different versions
as you will ship the correct procedure with the changes to the schema.
You can look at the INFORMATION_SCHEMA.columns view to find out what columns
a table has, although a different approach would be to only provide a
solution for the latest version and check they are on that version otherwise
force an upgrade.
John
"SA Development" wrote:

> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either ha
ve
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>Thanks for the good ideas everyone, sorry my first post wasn't entirely
clear.
Lets say I have a table like this:
field1(key) field2 field3 field4 field5
9999 a b c d
What I was hoping to do is add a new record (field1=123 for example), but
use the 9999 as a template record so that its field values are inserted into
my new record EXCEPT for any that I override.
It sounds like the update command will let me update just the fields I want,
is there a way to tell SQL to copy record 9999 to record 123 and use all the
field values in record 9999 ?
Thanks,
Alan|||Hi David,

> INSERT INTO YourTable (col1, col2, col3, col4, col5)
> SELECT 123, col2, col3, col4, col5
> FROM YourTable
> WHERE col1 = 9999
Thank you -- that is exactly what I needed!
Thanks to everyone else who posted as well.
Have a great day,
Alan

2005: displaying multi-select parameter values in a text box

I am using Reporting Services 2005. My parameter is a multi-select one. I
want to display what parameters the user chose when they run the report so
others know what data the report was filtered by. When I put the below text
string in a text box, I get an error. When I put the index value after the
string it shows that item if there is one for that index #, and an error if
there isn't. If they chose 5 items for that parameter, I want all 5 to show
up automatically and not have to put the index number for each one to display
it.
=Parameters.pState.value (this gives a query error)
=Parameters.pState.value(3) (this displays the value of the 4th parameter
value they chose. If they didn't choose 4 parameters, then it gives an array
out of bounds error)
If they chose 4 items, I want a piece of code that will automatically show
all 4 (e.g. AZ, NM, NY, FL)
thanks in advance!If you change a report parameter to be multi value, the .Value property will
return an object[] rather than an object. Hence you can no longer e.g. write
expressions like =Parameters!MVP1.Value.ToString().
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
Hope this helps,
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:2BE1BECC-8CDC-4C0A-9735-0CB291BDD301@.microsoft.com...
>I am using Reporting Services 2005. My parameter is a multi-select one. I
> want to display what parameters the user chose when they run the report so
> others know what data the report was filtered by. When I put the below
> text
> string in a text box, I get an error. When I put the index value after
> the
> string it shows that item if there is one for that index #, and an error
> if
> there isn't. If they chose 5 items for that parameter, I want all 5 to
> show
> up automatically and not have to put the index number for each one to
> display
> it.
> =Parameters.pState.value (this gives a query error)
> =Parameters.pState.value(3) (this displays the value of the 4th parameter
> value they chose. If they didn't choose 4 parameters, then it gives an
> array
> out of bounds error)
> If they chose 4 items, I want a piece of code that will automatically show
> all 4 (e.g. AZ, NM, NY, FL)
> thanks in advance!|||Thank you Robert. You are a God among men! This was SO helpful...
"Robert Bruckner [MSFT]" wrote:
> If you change a report parameter to be multi value, the .Value property will
> return an object[] rather than an object. Hence you can no longer e.g. write
> expressions like =Parameters!MVP1.Value.ToString().
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
>
> Hope this helps,
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:2BE1BECC-8CDC-4C0A-9735-0CB291BDD301@.microsoft.com...
> >I am using Reporting Services 2005. My parameter is a multi-select one. I
> > want to display what parameters the user chose when they run the report so
> > others know what data the report was filtered by. When I put the below
> > text
> > string in a text box, I get an error. When I put the index value after
> > the
> > string it shows that item if there is one for that index #, and an error
> > if
> > there isn't. If they chose 5 items for that parameter, I want all 5 to
> > show
> > up automatically and not have to put the index number for each one to
> > display
> > it.
> >
> > =Parameters.pState.value (this gives a query error)
> > =Parameters.pState.value(3) (this displays the value of the 4th parameter
> > value they chose. If they didn't choose 4 parameters, then it gives an
> > array
> > out of bounds error)
> >
> > If they chose 4 items, I want a piece of code that will automatically show
> > all 4 (e.g. AZ, NM, NY, FL)
> >
> > thanks in advance!
>
>