Truncate Milliseconds

[David]

Is there anyway to Truncate Milliseconds off a datetime value (type:
double)?

[Donald Lessau]

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000

Don

[Nobody]

Post by Donald Lessau

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000
Don

There is no such thing as "DateTime.Now" in VB6 or earlier. This newsgroup
is for VB Classic only, VB6 or lower. For VB.Net, try this group:

news://microsoft.public.dotnet.languages.vb

[Bob O`Bob]

Post by Nobody

Post by Donald Lessau

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000
Don

There is no such thing as "DateTime.Now" in VB6 or earlier. This newsgroup
news://microsoft.public.dotnet.languages.vb

Actually that part works fine.

The math in there won't actually DO what one might intuitively expect,
but the code is NOT dotnet-specific. You really should actually *test*
such things in VB6 before you pronounce them foreign.



Bob
--

[Rick Raisley]

Post by Bob O`Bob
The math in there won't actually DO what one might intuitively expect,
but the code is NOT dotnet-specific. You really should actually *test*
such things in VB6 before you pronounce them foreign.

I see that. But couldn't find any "DateTime" functions in any of my VB6
reference books, including the MS ones. And DateTime in the MSDN
documentation seems to refer to SQL commands (plus, despite installing the
FULL MSDN help, I've never been able to get most of the stuff available and
not grayed out).

A search online gives Microsoft's documentation for this as part of the .Net
Framework Class Library. So it seems like an honest mistake about thinking
it didn't belong to VB6. I never heard of it, myself. Do you have a good
reference that outlines the syntax of all the options?

[Schmidt]

Post by Rick Raisley
But couldn't find any "DateTime" functions in any of my VB6
reference books, ...

It's just a VBA-Namespace (a "Module-name").

Post by Rick Raisley
Do you have a good reference that outlines the syntax of
all the options?

Please look into your ObjectExplorer - also note the other
namespaces (groups of functions/functionality if you want)
as e.g. Strings, FileSystem, Math, ... etc.

Olaf

[Rick Raisley]

I see that now, thanks Olaf. Just curious: I have lots of VB books, that
I've read through just to pick up new ideas, and none of these VB-only
subjects are covered. Does anyone know of a good book that /does/ cover
things like DateTime, Math, etc. (non native VB functions)?
--
Regards,

Rick Raisley
heavymetal-A-T-bellsouth-D-O-T-net

Post by Schmidt

Post by Rick Raisley
But couldn't find any "DateTime" functions in any of my VB6
reference books, ...

It's just a VBA-Namespace (a "Module-name").

Post by Rick Raisley
Do you have a good reference that outlines the syntax of
all the options?

Please look into your ObjectExplorer - also note the other
namespaces (groups of functions/functionality if you want)
as e.g. Strings, FileSystem, Math, ... etc.
Olaf

[Karl E. Peterson]

Post by Rick Raisley
I see that now, thanks Olaf. Just curious: I have lots of VB books, that
I've read through just to pick up new ideas, and none of these VB-only
subjects are covered. Does anyone know of a good book that /does/ cover
things like DateTime, Math, etc. (non native VB functions)?

Never seen such a beast. But really, I'm not sure that info is useful in any
context other than being able to quickly find stuff in the Object Browser without
using Search, is it?

[Rick Raisley]

Post by Karl E. Peterson

Post by Rick Raisley
I see that now, thanks Olaf. Just curious: I have lots of VB books, that
I've read through just to pick up new ideas, and none of these VB-only
subjects are covered. Does anyone know of a good book that /does/ cover
things like DateTime, Math, etc. (non native VB functions)?

Never seen such a beast. But really, I'm not sure that info is useful in
any context other than being able to quickly find stuff in the Object
Browser without using Search, is it?

Well, isn't that a little like saying that VB books aren't necessary,
because the Object Browser has all the VB classes and members? I guess I
learn better by example, and when books have articles/code on how-to's,
especially using common commands in uncommon ways, I find that quite useful.
Especially since a lot of the VBA functions appear to mimic or duplicate VB
functions, I never thought to look at them for an /expansion/ of VB.
Something outlining what /additional/ functionality there is might be nice.
;-)

[Karl E. Peterson]

Post by Rick Raisley

Post by Karl E. Peterson

Post by Rick Raisley
I see that now, thanks Olaf. Just curious: I have lots of VB books, that
I've read through just to pick up new ideas, and none of these VB-only
subjects are covered. Does anyone know of a good book that /does/ cover
things like DateTime, Math, etc. (non native VB functions)?

Never seen such a beast. But really, I'm not sure that info is useful in
any context other than being able to quickly find stuff in the Object
Browser without using Search, is it?

Well, isn't that a little like saying that VB books aren't necessary,
because the Object Browser has all the VB classes and members?

I don't think so, because the tidbit in question is utterly useless other than to
disambiguate redundant names.

Post by Rick Raisley
I guess I
learn better by example, and when books have articles/code on how-to's,
especially using common commands in uncommon ways, I find that quite useful.

I can appreciate that.

Post by Rick Raisley
Especially since a lot of the VBA functions appear to mimic or duplicate VB
functions, I never thought to look at them for an /expansion/ of VB.
Something outlining what /additional/ functionality there is might be nice.
;-)

Do you find additional functionality here?

[Rick Raisley]

Post by Rick Raisley
A search online gives Microsoft's documentation for this as part of the
.Net Framework Class Library. So it seems like an honest mistake about
thinking it didn't belong to VB6. I never heard of it, myself. Do you have
a good reference that outlines the syntax of all the options?

Or, I could hit F2 and view the members of the DateTime class in the Object
Explorer. ;-)

Still, none of my books even mentions it. Guess that's as its really a
Windows thing, not a VB thing.

[Donald Lessau]

Post by Donald Lessau

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000
Don

There is no such thing as "DateTime.Now" in VB6 or earlier...

There is. Type "DateTime." in the direct window and learn... ;)

It is not necessary to state it though. This works just as well:
Fix(Now * 10000000) / 10000000

Don

[dpb]

Post by Donald Lessau

Post by Donald Lessau

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000
Don

There is no such thing as "DateTime.Now" in VB6 or earlier...

There is. Type "DateTime." in the direct window and learn... ;)
Fix(Now * 10000000) / 10000000

But I don't think it does what OP wants.

Easiest (from coding standpoint) I can come up w/ would be to Format$()
to hh:mm:ss.ss and the reconvert back to Date double...

--

[Donald Lessau]

Post by dpb

Post by Donald Lessau

Post by Donald Lessau

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000
Don

There is no such thing as "DateTime.Now" in VB6 or earlier...

There is. Type "DateTime." in the direct window and learn... ;)
Fix(Now * 10000000) / 10000000

But I don't think it does what OP wants.

If OP wants to truncate milliseconds, then the code does what OP wants.

To reconvert it to a Date:
CDate(Fix(Now * 10000000) / 10000000)

As a proof that the milliseconds are truncated, try this:
Debug.Print CDbl(CDate(Fix(Now * 10000000) / 10000000)), CDbl(Now)

Don

[Jim Mack]

Post by Donald Lessau

Post by dpb

Post by Donald Lessau
Fix(Now * 10000000) / 10000000

But I don't think it does what OP wants.

If OP wants to truncate milliseconds, then the code does what OP wants.

Post by dpb

Post by Donald Lessau
Fix(Now * 10000000) / 10000000

Where does the 10000000 come from? Date variables store the time part
as a fraction of a day, which is 86400000 milliseconds. So to
accurately truncate milliseconds, it seems that you'd have to work
that into the mix.

Fix(Now * 86400000) / 86400000

Or something like that, yes?

[Larry Serflaten]

Post by Jim Mack

Post by Donald Lessau
Fix(Now * 10000000) / 10000000

Where does the 10000000 come from? Date variables store the time part
as a fraction of a day, which is 86400000 milliseconds. So to
accurately truncate milliseconds, it seems that you'd have to work
that into the mix.
Fix(Now * 86400000) / 86400000
Or something like that, yes?

That's a bit large, isn't it? I would think asking to "truncate milliseconds"
would mean round to the nearest second, not round to the nearest millisecond....

? DateDiff("s", 0, 1) ' 86400

LFS

[dpb]

Post by Larry Serflaten

Post by Jim Mack

Post by Donald Lessau
Fix(Now * 10000000) / 10000000

Where does the 10000000 come from? Date variables store the time part
as a fraction of a day, which is 86400000 milliseconds. So to
accurately truncate milliseconds, it seems that you'd have to work
that into the mix.
Fix(Now * 86400000) / 86400000
Or something like that, yes?

That's a bit large, isn't it? I would think asking to "truncate milliseconds"
would mean round to the nearest second, not round to the nearest millisecond....
? DateDiff("s", 0, 1) ' 86400

Well, I guess it could be ambiguous; my initial read was to round to
centiseconds, not whole seconds. That was based on "truncate" not
"round" but only OP knows for sure...

--

[Larry Serflaten]

Post by dpb
Well, I guess it could be ambiguous; my initial read was to round to
centiseconds, not whole seconds. That was based on "truncate" not
"round" but only OP knows for sure...

I kinda thought the whole idea was academic. What purpose could there
be for removing milliseconds if not for display purposes. When used for
display, the Format function should be all that is needed....

LFS

[dpb]

Post by Larry Serflaten

Post by dpb
Well, I guess it could be ambiguous; my initial read was to round to
centiseconds, not whole seconds. That was based on "truncate" not
"round" but only OP knows for sure...

I kinda thought the whole idea was academic. What purpose could there
be for removing milliseconds if not for display purposes. When used for
display, the Format function should be all that is needed....

Ah, there ya' go again... :)

I suppose one could be formatting before storage to a database to a
fixed precision or some such would be one use.

--

[Jim Mack]

Post by Larry Serflaten

Post by Jim Mack

Post by Donald Lessau
Fix(Now * 10000000) / 10000000

Where does the 10000000 come from? Date variables store the time
part as a fraction of a day, which is 86400000 milliseconds. So to
accurately truncate milliseconds, it seems that you'd have to work
that into the mix.
Fix(Now * 86400000) / 86400000
Or something like that, yes?

That's a bit large, isn't it? I would think asking to "truncate
milliseconds" would mean round to the nearest second, not round to
the nearest millisecond....
? DateDiff("s", 0, 1) ' 86400

The question wasn't that clear, I guess -- I was reacting to DL's
10000000, which seemed to be answering the question, 'how do you
truncate to the nearest millisecond'.

Since the real resolution of Now doesn't seem to be anything like 1ms,
I suppose it's overkill. "Adjust the number of zeros until you get the
desired result." (-:

[Larry Serflaten]

Post by Donald Lessau
Debug.Print CDbl(CDate(Fix(Now * 10000000) / 10000000)), CDbl(Now)

I wouldn't call reducing the number of decimal places a truncation when
dealing with Dates. With Dates, a second is not .0000001 but rather
.000011574074074074074074074(1)

Consider someone asking to trucate quarters from a Currency value. Can
you just go: Value = Int(Value * 10) / 10 ???

Such a solution ends up with values like 1.3, 5.9, 4.8 etc. Its a truncation
by definition, but not to the value requested.

So the question is, like rounding down to the nearest quarter, does the OP
really mean to round down to the nearest second? If that is the case then
I would suggest the value 86400 should be used in place of your 10000000.

LFS

[Donald Lessau]

Post by Larry Serflaten

Post by Donald Lessau
Debug.Print CDbl(CDate(Fix(Now * 10000000) / 10000000)), CDbl(Now)

I wouldn't call reducing the number of decimal places a truncation when
dealing with Dates. With Dates, a second is not .0000001 but rather
.000011574074074074074074074(1)
Consider someone asking to trucate quarters from a Currency value. Can
you just go: Value = Int(Value * 10) / 10 ???
Such a solution ends up with values like 1.3, 5.9, 4.8 etc. Its a truncation
by definition, but not to the value requested.
So the question is, like rounding down to the nearest quarter, does the OP
really mean to round down to the nearest second? If that is the case then
I would suggest the value 86400 should be used in place of your 10000000.

Oh yes, I see. My apologies to the OP! So I think you are right, this should
be the way to go (using CDec to reduce rounding errors):
dateMsecsTruncated = Int(CDec(dateWithMsecs) * 86400) / 86400

Don

[Donald Lessau]

Well, when I do this repeatedly in the direct window I get a 1 second
difference in maybe 50% of all cases:
? CDate(Int(CDec(Now) * 86400) / 86400), Now

So, the formula is still not perfect, and I will officially give up at this
moment. ;-)

Don

[Donald Lessau]

Post by Donald Lessau
Well, when I do this repeatedly in the direct window I get a 1 second
? CDate(Int(CDec(Now) * 86400) / 86400), Now
So, the formula is still not perfect, and I will officially give up at
this moment. ;-)

Okay, got it. The 1 second difference in 50% of all cases was because Now
itself apparently rounds milliseconds before being converted to a date
string. If you add rounding to the formula (as already shown by Jim Mack in
another post) then everything is perfect:

? CDate((Int(CDbl(Now) * 86400# + 0.5#)) / 86400#), Now 'same date display

So, two formulas:
dateMsecsTruncated = Int(CDbl(dateWithMsecs) * 86400#) / 86400#
dateMsecsRounded = Int(CDbl(dateWithMsecs) * 86400# + 0.5#) / 86400#

Don

[David]

I'm the OP.

I need to truncate milliseconds before storing in a Access DB, as my .Seek
is returning a .NoMatch condition, which then cause another record with the
same datetime.

Since Access stores dates as doubles, but Only displays to seconds,
a need for truncating milliseconds is needed. Rounding to seconds also may
be an issue depending on the number of milliseconds.

Post by Donald Lessau

Post by David
double)?

Fix(DateTime.Now * 10000000) / 10000000
Don

[Jim Mack]

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as
my .Seek is returning a .NoMatch condition, which then cause
another record with the same datetime.
Since Access stores dates as doubles, but Only displays to seconds,
a need for truncating milliseconds is needed. Rounding to seconds
also may be an issue depending on the number of milliseconds.

The solution for rounding is slightly different from the one for
truncating. For truncating to seconds,

NewDate = CDate((Int(CDbl(Now) * 86400#)) / 86400#)

To round,

NewDate = CDate((Int((CDbl(Now) * 86400#) + 0.5#)) / 86400#)

Some of those signs and parens are redundant, shown for clarity. Air
code, test first.

[Larry Serflaten]

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as my .Seek
is returning a .NoMatch condition, which then cause another record with the
same datetime.

Then this might be close to what you need to normalize your values
before storing them to the DB:

Function ExactDate(TimeStamp As Variant) As Variant
Dim n As Date
n = CDate(TimeStamp)
ExactDate = DateSerial(Year(n), Month(n), Day(n)) _
+ TimeSerial(Hour(n), Minute(n), Second(n))
End Function


Try it and see....
LFS

[Donald Lessau]

Post by Larry Serflaten

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as my .Seek
is returning a .NoMatch condition, which then cause another record with the
same datetime.

Then this might be close to what you need to normalize your values
Function ExactDate(TimeStamp As Variant) As Variant
Dim n As Date
n = CDate(TimeStamp)
ExactDate = DateSerial(Year(n), Month(n), Day(n)) _
+ TimeSerial(Hour(n), Minute(n), Second(n))
End Function
Try it and see....

Tried it, works well. It actually rounds the milliseconds rather than
truncate. The result is indentical to this one (which might be faster -- air
hypothesis...):

Function ExactDateRound(TimeStamp As Date) As Date
ExactDateRound = CDate(Int(CDbl(TimeStamp) * 86400# + 0.5) / 86400#)
End Function

Don

[David]

Thanks All.

Will try both.

First time I've encountered this issue.

I believe truncate is better though, as rounding may result in different
time values.

Debating whether I should change type:DateTime to string (YYYYMMDDHHMMSS
timezone). Cause more work type casting
as this large App "was" done, but can't have DB errors.

Any thoughts / recommendations.

Post by Donald Lessau

Post by Larry Serflaten

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as my .Seek
is returning a .NoMatch condition, which then cause another record with the
same datetime.

Then this might be close to what you need to normalize your values
Function ExactDate(TimeStamp As Variant) As Variant
Dim n As Date
n = CDate(TimeStamp)
ExactDate = DateSerial(Year(n), Month(n), Day(n)) _
+ TimeSerial(Hour(n), Minute(n), Second(n))
End Function
Try it and see....

Tried it, works well. It actually rounds the milliseconds rather than
truncate. The result is indentical to this one (which might be faster --
Function ExactDateRound(TimeStamp As Date) As Date
ExactDateRound = CDate(Int(CDbl(TimeStamp) * 86400# + 0.5) / 86400#)
End Function
Don

[Bob O`Bob]

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as my .Seek
is returning a .NoMatch condition, which then cause another record with the
same datetime.
Since Access stores dates as doubles, but Only displays to seconds,
a need for truncating milliseconds is needed. Rounding to seconds also may
be an issue depending on the number of milliseconds.

In my opinion, you will never find a proper solution through any sort of
"normalization" of real number data. Instead, fix the lookup criteria,
so it can deal with a reasonable range of time, rather than demanding
an exact match.



Bob
--

[James Hahn]

If you are rounding to seconds you could extract the year, month, day, hour,
minute and seconds as separate items and put them back into a new datetime.

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as my .Seek
is returning a .NoMatch condition, which then cause another record with
the same datetime.
Since Access stores dates as doubles, but Only displays to seconds,
a need for truncating milliseconds is needed. Rounding to seconds also
may be an issue depending on the number of milliseconds.

[David]

Implemented Mr. Mack's suggestion.
So far go good in test mode. Will put in production today so we will see.

Mr. Hahn: Haven't tried you suggestion but believe will end up with same
problem as the original -- when the system reassembles the date as a double,
it carries the precision out -- which is the reason for the problem

Finally found a KB that addresses the precision issue for dates in Access,
but NO solution(s) is provided. Commented back on the KB so we'll see if
any revision takes place and a solution offered.

David

Post by James Hahn
If you are rounding to seconds you could extract the year, month, day,
hour, minute and seconds as separate items and put them back into a new
datetime.

Post by David
I'm the OP.
I need to truncate milliseconds before storing in a Access DB, as my
.Seek is returning a .NoMatch condition, which then cause another record
with the same datetime.
Since Access stores dates as doubles, but Only displays to seconds,
a need for truncating milliseconds is needed. Rounding to seconds also
may be an issue depending on the number of milliseconds.

[James Hahn]

Truncate or round the seconds before re-assembling.

Post by David
Implemented Mr. Mack's suggestion.
So far go good in test mode. Will put in production today so we will see.
Mr. Hahn: Haven't tried you suggestion but believe will end up with same
problem as the original -- when the system reassembles the date as a
double, it carries the precision out -- which is the reason for the
problem
Finally found a KB that addresses the precision issue for dates in Access,
but NO solution(s) is provided. Commented back on the KB so we'll see if
any revision takes place and a solution offered.
David