problem with date types

  • Thread starter Thread starter Rob T
  • Start date Start date
R

Rob T

I'm storing a date/time into a SQL table of type datetime. I need it to be
precise so the value is stored to the 1000th of a second. ie "insert into
myTable mydate values ('08/05/2005 2:56:11.987'). This works fine...if you
check the value in the table with query analyzer, it shows in there
properly.

Now, in my app, I'm executing the same query (that I used in QA) and storing
tha in a dataview. If I break and watch the program, the value that's
stored in the dataview is #8/5/2005 2:56:11 PM# with a datatype of
date....so it's truncating my fractions of a second!!!

How can I get my missing time? Thanks.
 
Rob T said:
I'm storing a date/time into a SQL table of type datetime. I need it to
be precise so the value is stored to the 1000th of a second. ie "insert
into myTable mydate values ('08/05/2005 2:56:11.987'). This works
fine...if you check the value in the table with query analyzer, it shows
in there properly.

Now, in my app, I'm executing the same query (that I used in QA) and
storing tha in a dataview. If I break and watch the program, the value
that's stored in the dataview is #8/5/2005 2:56:11 PM# with a datatype of
date....so it's truncating my fractions of a second!!!
Click to expand...

Make sure you read the "Remarks" section of the "'DateTime' structure"
chapter of the documentation.
 
I assume you're referring to the fact that the datetime only goes to the
nearest second...or am I missing something? I was hoping that when the
datastructure of the dv was created that I could store it as a string....?
 
Rob,

Are you sure of that, what you see is the US representation (which make
people outside that sometimes crazy) in the IDE.

The dateTime itself is a format in long, containing ticks starting at the
year that the Georgian Calendar started to be used in England (and therefore
in its colonies from which the US was one at that time).

I hope this helps,

Cor
 
(head spinning) I just wanted to know how I can get a date field with
fractions of a second into my dataview..........

-Regards from New England!
 
Rob T said:
(head spinning) I just wanted to know how I can get a date field
with fractions of a second into my dataview..........
Click to expand...


In your dataview, you don't have any information stored. Probably a
datatable stores it. This also includes the fractions of a second. Your
problem is probably converting the value into a string including the
fractions.

We don't know how you convert the value or have it converted to a string,
thus it's hard to give a suggestion. If you use the Date's ToString method,
have a look at the ToString function's documentation how to include the
fractions. There's also a link to this chapter:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpcondatetimeformatstrings.asp


Armin
 
Rob,
In addition to the other comments:

| date....so it's truncating my fractions of a second!!!
Are you certain that it is truncating them as opposed to simply not showing
them?

See the remarks at:

http://msdn.microsoft.com/library/d.../cpref/html/frlrfsystemdatetimeclasstopic.asp

<quote>
DateTime values are measured in 100-nanosecond units called ticks, and a
particular date is the number of ticks since 12:00 midnight, January 1, 1,
A.D. (C.E.)
</quote>

100-nanosecond units should be more then enough to cover 1000th of a second!
Remember that a nanosecond is one billionth (10 to -9th) of a second.

DateTime values by default are only displayed to seconds, however this is
normally only a Display Issue! If you want the to see the 100-nanosecond
units, you need to use a custom DateTime format.

http://msdn.microsoft.com/library/d...ide/html/cpconcustomdatetimeformatstrings.asp

For example:

Dim aDate As DateTime = DirectCast(theDataView!theDateColumn,
DateTime)

Debug.WriteLine(aDate.ToString(), "default formatting")
Debug.WriteLine(aDate.ToString("yyyy.MM.dd hh:mm:ss.fffffff"),
"custom formatting")

The ".fffffff" says to display seconds fractions to the full seven digits
(100-nanoseconds), try the above two lines with the Date column in your
DataView. Try the above two lines with DateTime.Now

Dim aDate As DateTime = DateTime.Now

Debug.WriteLine(aDate.ToString(), "default formatting")
Debug.WriteLine(aDate.ToString("yyyy.MM.dd hh:mm:ss.fffffff"),
"custom formatting")

I suspect your fractions of seconds are not being truncated, rather they are
simply not being displayed!

Depending on how you are displaying the date you need to include a "format"
that includes fractions of seconds. For example with the Windows Forms
DataGrid, you can add a TableStyle for your table, then add a
DataGridTextBoxColumn for your date column. You can then set
DataGridTextBoxColumn.Format to a custom format that includes the fractions
of seconds you want displayed...


Hope this helps
Jay

| I'm storing a date/time into a SQL table of type datetime. I need it to
be
| precise so the value is stored to the 1000th of a second. ie "insert
into
| myTable mydate values ('08/05/2005 2:56:11.987'). This works fine...if
you
| check the value in the table with query analyzer, it shows in there
| properly.
|
| Now, in my app, I'm executing the same query (that I used in QA) and
storing
| tha in a dataview. If I break and watch the program, the value that's
| stored in the dataview is #8/5/2005 2:56:11 PM# with a datatype of
| date....so it's truncating my fractions of a second!!!
|
| How can I get my missing time? Thanks.
|
|
 
Rob,

Before this is misunderstood.
http://msdn.microsoft.com/library/d.../cpref/html/frlrfsystemdatetimeclasstopic.asp

<quote>
DateTime values are measured in 100-nanosecond units called ticks, and a
particular date is the number of ticks since 12:00 midnight, January 1, 1,
A.D. (C.E.)
</quote>
Click to expand...

Be aware that this is for system.datetime. SQL server datetime is as I tried
to describe it.
I always forget that date therefore :-)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.aspI hope this helps,Cor
 
Cor,
My point is that System.DateTime has create precision (uses smaller units)
then the DateTime on SQL Server.

Rob already mention that his SQL data had "1000th of a second" precision.

Hope this helps
Jay

| Rob,
|
| Before this is misunderstood.
| >
| >
http://msdn.microsoft.com/library/d.../cpref/html/frlrfsystemdatetimeclasstopic.asp
| >
| > <quote>
| > DateTime values are measured in 100-nanosecond units called ticks, and a
| > particular date is the number of ticks since 12:00 midnight, January 1,
1,
| > A.D. (C.E.)
| > </quote>
|
| Be aware that this is for system.datetime. SQL server datetime is as I
tried
| to describe it.
| I always forget that date therefore :-)
|
|
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.aspI
hope this helps,Cor
|
 
Jay B. Harlow said:
Cor,
My point is that System.DateTime has create precision (uses smaller units)
then the DateTime on SQL Server.

Rob already mention that his SQL data had "1000th of a second" precision.
Click to expand...

I saw that you showed it, I did not do any addition or comment to that.

However that the starting date of datetime is not always the same can be
confusing if you have first to handle with datetimes and therefore my
comment. It could have been seen by others than you or me that you made a
correction to my message about the start of the date in a SQL server.

Cor
 
Doh!
| My point is that System.DateTime has create precision (uses smaller units)
| then the DateTime on SQL Server.
That's has *greater* precision.

Jay

| Cor,
| My point is that System.DateTime has create precision (uses smaller units)
| then the DateTime on SQL Server.
|
| Rob already mention that his SQL data had "1000th of a second" precision.
|
| Hope this helps
| Jay
|
| || Rob,
||
|| Before this is misunderstood.
|| >
|| >
|
http://msdn.microsoft.com/library/d.../cpref/html/frlrfsystemdatetimeclasstopic.asp
|| >
|| > <quote>
|| > DateTime values are measured in 100-nanosecond units called ticks, and
a
|| > particular date is the number of ticks since 12:00 midnight, January 1,
| 1,
|| > A.D. (C.E.)
|| > </quote>
||
|| Be aware that this is for system.datetime. SQL server datetime is as I
| tried
|| to describe it.
|| I always forget that date therefore :-)
||
||
|
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.aspI
| hope this helps,Cor
||
|
|
 
Rob T said:
I assume you're referring to the fact that the datetime only goes to the
nearest second...or am I missing something?
Click to expand...

No, I was referring to this paragraph:

| Time values are measured in 100-nanosecond units called ticks

You can determine the number of ticks by querying the 'Tick' property of
your 'DateTime' object.
 
Back
Top