I do not think Access is able to distinguish milliseconds.
It is. But custom code is needed, which can be found here:
http://www.devx.com/dbzone/Article/39046/1954
The download includes a function that will round millisecond values to those of SQL Server:
Public Function DateTimeRoundMsec( _
ByVal datDate As Date, _
Optional booRoundSqlServer As Boolean, _
Optional booRoundSecondUp As Boolean) _
As Date
' Returns datDate rounded to the nearest millisecond approximately by 4/5.
' The dividing point for up/down rounding may vary between 0.3 and 0.7ms
' due to the limited resolution of data type Double.
'
' If booRoundSqlServer is True, milliseconds are rounded by 3.333ms to match
' the rounding of the datetime data type of SQL Server - to 0, 3 or 7 as the
' least significant digit:
'
' Msec SqlServer
' 0 0
' 1 0
' 2 3
' 3 3
' 4 3
' 5 7
' 6 7
' 7 7
' 8 7
' 9 10
' 10 10
etc.
/gustav
david wrote:
That is a bad idea.In Access, DateTimes are Floating Point Numbers.
15-Jan-10
That is a bad idea
In Access, DateTimes are Floating Point Numbers
In SQL Server, DateTimes are Fixed Point number
If you MUST do this, you MUST NOT convert to an Access DateTime
If you MUST do this, you MUST cast the SQL Server DateTime to an integer o
to a string
When you convert between Fixed Point and Floating Point numbers, you ALWAY
get some error in the fractional part
In Access, the fractional part is the time. The DateTime is stored in
Doubl
In SQL Server, you have 4 bytes (out of 8) storing the number of ticks pas
midnight, to an accuracy of 3.33 msec (that is the msec values are 0, 3
7... not 0,1,2,3,4,5,6,7). You do not have msec accuracy
Unless you have a separate primary key field, if you try to write the dat
back to SQL Server you will get an error, because the act of converting fro
fixed point (base 1/300) to floating point (base 2), then back to fixe
point (base 1/300), has changed the data
If you do have a separate primary key field, writing back will change th
data without an error
This is NOT because SQL Server has msec (it does not), or because Acces
does not have msec (it does), or because Access does not display msec (yo
can). It is because the computer cannot accurately convert between tick
(1/300th of a second) and binary numbers
No amount of fiddling with the display or with the time values will fix tha
problem
The normal solution is "Don't use DateTime values as an index"
The work-around is "If you must use it as an index, do not use it as
DateTime
Note, if you look at the numeric value of the Date in SQL Server, it is of
by one from the numeric value in Access. If you start poking number
(instead of dates) into the Date field, you will have to handle this you
self
(david)
Previous Posts In This Thread:
SQL date and milliseconds
Hi, using Access 2003 and SQL 2005
In sql I have a field of datetime data type. It contains a value '2010-01-1
15:29:48.060'
I want to create a select statement to pass through to sql with this valu
as a where condition to return this record
The problem is that in Access the record with this date value is loaded a
'13/01/2010 3:29:48 p.m.' (New Zealand/British date format). That is, th
milliseconds appear to have been stripped off
I can use the following to format the Access date valu
"'" & Format(dateValue, "yyyy-mm-dd") & Space(1) & Hour(dateValue) & ":"
Minute(dateValue) & ":" & Second(dateValue) & "'
However this does not include the milliseconds part
How do I create a select statement where condition that is passed through
to sql to return/update the correct record
Any ideas or recommendations appreciated :-
Many thanks
Jonathan
Hi Jonathan,I do not think Access is able to distinguish milliseconds.
Hi Jonathan
I do not think Access is able to distinguish milliseconds
See
http://support.microsoft.com/kb/21027
Applying a particular datetime format to a stored value
does not make any difference because, as the above article
says:
"Access stores the Date/Time data type as a
double-precision, floating-point number up to 15 decimal
places. The integer part of the double-precision number
represents the date. The decimal portion represents the
time."
Formatting the number simply displays it in the way you want
without changing the underlying value.
The article concludes with some methods for ensuring
accurate results.
I am not sure this helps you too much because it seems you
need millisecond accuracy.
Geoff
message
Jonathan.
Jonathan.
What I would recommend is looking at the raw data type in the Access
Date/Time fields. It???s not a date at all but a double which expresses
duration of time as simply a number of days.
There is no Date stored and there is no Time stored. The duration, number of
days, is added to base zero date/time. More on the subject in the following
link: -
http://blogs.msdn.com/ericlippert/archive/2003/09/16/eric-s-complete-guide-to-vt-date.aspx
If you wish to calculate the Millisecond offset you can play with this: -
Sub Test()
Dim datMyDate As Date
Dim dblMillSec As Double
Const conMilliseconds As Double = 1 / 86400 / 1000
' Set the date.
datMyDate = #1/13/2010 3:29:48 PM#
' Show the raw date value.
MsgBox CDbl(datMyDate) ' < 40191.6456944444
' Calculate the 60 Milliseconds double.
dblMillSec = conMilliseconds * 60
' Add the Milliseconds to the Date.
datMyDate = datMyDate + dblMillSec
' Show the raw date value.
MsgBox CDbl(datMyDate) ' < 40191.6456951389
End Sub
On the other hand, some systems write to Access Tables storing the number of
days and appends a new Field as the offset expressed in Milliseconds. RSView
for one handles it that way.
--
A nod is as good as a wink to a blind horse.
:
Jonathan,You could try the following function
ublic Function
Jonathan,
You could try the following function:
Public Function FormatSQLDate(value As Date) As String
Dim millies As Integer
Dim dblTime As Double
dblTime = CDbl(datDate)
' Remove date part from date/time value and extract count of milliseconds.
' Note the use of CDec() to prevent bit errors for very large date values.
millies = Abs(dblTime - CDec(Fix(dblTime))) * clngMillisecondsPerDay Mod
1000
FormatSQLDate = Format(value, "yyyy-mm-dd hh:nn:ss.") & Format(millies,
"000")
End Function
Note: the idea came from the following article:
http://www.devx.com/dbzone/Article/39046/0/page/1
Hope this helps
:
Thanks for you ideas and suggestions.Jonathan"Jonathan" wrote:
Thanks for you ideas and suggestions.
Jonathan
:
Hi Jonathan,You may be able to do it with a pass-through query or two.
Hi Jonathan,
You may be able to do it with a pass-through query or two. One to read
the data and the other to write the data. See <
http://www.accessmonster.com/Uwe/Fo...conds-in-a-date-field-that-is-in-a-SQL-Server
An alternate may be to get the milliseconds from SQL Server using the
Transact-SQL DatePart() function.
select DateTimeField, DatePart("ms", DateTimeField") as
DateTimeFieldMilliseconds ...
If those values are then stored in variables named dtDateTimeField and
intMilliseconds then you could do something like:
set strUpdateSQL = _
"update YourTable " & _
"set SomeOtherField = 1234 " & _
"where DateTimeField = DateAdd(""ms"", " & intMilliseconds & _
", #" & Format(dtDateTimeField, "yyyy-mm-dd hh:nn:ss") & "#)"
Note that the DateAdd() function is the Transact-SQL function and is
embedded into the string. So the resulting string would look something like:
update YourTable set SomeOtherField = 1234 where DateTimeField = DateAdd("ms",
123, #2010-01-01 18:30:45#)
You would need to use the DAO Database.Execute with the dbSQLPassThrough
option.
Hope that helps,
Clifford Bass
Jonathan wrote:
--
That is a bad idea.In Access, DateTimes are Floating Point Numbers.
That is a bad idea.
In Access, DateTimes are Floating Point Numbers.
In SQL Server, DateTimes are Fixed Point numbers
If you MUST do this, you MUST NOT convert to an Access DateTime.
If you MUST do this, you MUST cast the SQL Server DateTime to an integer or
to a string.
When you convert between Fixed Point and Floating Point numbers, you ALWAYS
get some error in the fractional part.
In Access, the fractional part is the time. The DateTime is stored in a
Double
In SQL Server, you have 4 bytes (out of 8) storing the number of ticks past
midnight, to an accuracy of 3.33 msec (that is the msec values are 0, 3,
7... not 0,1,2,3,4,5,6,7). You do not have msec accuracy.
Unless you have a separate primary key field, if you try to write the data
back to SQL Server you will get an error, because the act of converting from
fixed point (base 1/300) to floating point (base 2), then back to fixed
point (base 1/300), has changed the data.
If you do have a separate primary key field, writing back will change the
data without an error.
This is NOT because SQL Server has msec (it does not), or because Access
does not have msec (it does), or because Access does not display msec (you
can). It is because the computer cannot accurately convert between ticks
(1/300th of a second) and binary numbers.
No amount of fiddling with the display or with the time values will fix that
problem.
The normal solution is "Don't use DateTime values as an index".
The work-around is "If you must use it as an index, do not use it as a
DateTime"
Note, if you look at the numeric value of the Date in SQL Server, it is off
by one from the numeric value in Access. If you start poking numbers
(instead of dates) into the Date field, you will have to handle this your
self.
(david)
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Customized Find Control for FlowDocuments
http://www.eggheadcafe.com/tutorial...3-721a40cf910c/wpf-customized-find-contr.aspx