469,890 Members | 2,319 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

problem with date types

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.
Nov 19 '05 #1
12 1558
Mabye this hack will help you furhter. Try to cast the date to a string on
sql server so once its passed to asp.net it will accept the data as a whole
and not interpret it.

I hope it helps...

Cheers,
Tom Pester
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.

Nov 19 '05 #2
"Rob T" <RT*********@DONTwalchemSPAM.com> schrieb:
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!!!


Make sure you read the "Remarks" section of the "'DateTime' structure"
chapter of the documentation.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

Nov 19 '05 #3
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....?

"Herfried K. Wagner [MVP]" <hi***************@gmx.at> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
"Rob T" <RT*********@DONTwalchemSPAM.com> schrieb:
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!!!


Make sure you read the "Remarks" section of the "'DateTime' structure"
chapter of the documentation.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

Nov 19 '05 #4
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
Nov 19 '05 #5
(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!

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
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

Nov 19 '05 #6
"Rob T" <RT*********@DONTwalchemSPAM.com> schrieb
(head spinning) I just wanted to know how I can get a date field
with fractions of a second into my dataview..........

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...matstrings.asp
Armin

Nov 19 '05 #7
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/de...classtopic.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/de...matstrings.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

"Rob T" <RT*********@DONTwalchemSPAM.com> wrote in message
news:uV**************@TK2MSFTNGP14.phx.gbl...
| 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.
|
|
Nov 19 '05 #8
Rob,

Before this is misunderstood.

http://msdn.microsoft.com/library/de...classtopic.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/de...a-db_9xut.aspI hope this helps,Cor

Nov 19 '05 #9
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

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
| Rob,
|
| Before this is misunderstood.
| >
| >
http://msdn.microsoft.com/library/de...classtopic.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/de...a-db_9xut.aspI
hope this helps,Cor
|
Nov 19 '05 #10
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> schreef in bericht
news:O9**************@TK2MSFTNGP12.phx.gbl...
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.


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
Nov 19 '05 #11
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:O9**************@TK2MSFTNGP12.phx.gbl...
| 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
|
| "Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
| news:%2****************@tk2msftngp13.phx.gbl...
|| Rob,
||
|| Before this is misunderstood.
|| >
|| >
|
http://msdn.microsoft.com/library/de...classtopic.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/de...a-db_9xut.aspI
| hope this helps,Cor
||
|
|
Nov 19 '05 #12
"Rob T" <RT*********@DONTwalchemSPAM.com> schrieb:
I assume you're referring to the fact that the datetime only goes to the
nearest second...or am I missing something?


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.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

Nov 19 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by RJS | last post: by
8 posts views Thread by Gerrit Holl | last post: by
7 posts views Thread by jslowery | last post: by
1 post views Thread by Jim | last post: by
1 post views Thread by php newbie | last post: by
3 posts views Thread by Dominic Messenger | last post: by
11 posts views Thread by Geoff Jones | last post: by
5 posts views Thread by pradeep84 | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.