update time value issue

  • Thread starter Thread starter iccsi
  • Start date Start date
I

iccsi

I have following code to update my table only time value from
datetimepicker

strSQLString = " UPDATE mytable " & _
" ShiftStart = #" &
FormatDateTime(dtpShiftStartTime.Value - Int(dtpShiftStartTime.Value),
vbShortTime) & "#," & _
" WHERE " & _
" ReasonID = " & MyPK

The backend is SQL server 2000 datetime field.

The result always inlcude date and time.
I use debug.print and get SQL running the SQL from query and it works
from query that the table only store time value.

it works in the code when I insert a new record. I use same code but
using insert into then the table get time value only.

For some reason, it always inlcude date value and time value in my
table when I use VBA code to update the SQL server table.

Your information and help is great appreciated,

iccsi
 
DateTime fields in SQL Server (and in ACCESS, for that matter) store a date
and time always. This is because dates/times are stored as decimal numbers,
where the integer portion represents the number of days since a fixed date
(differs for ACCESS and SQL Server) and the decimal (fraction) portion
represents the fraction of "time" within a 24-hour period (midnight to
midnight).

So for example, in ACCESS, the datetime value of 12/16/2011 5:56:17 PM
(December 16) is actually stored as 40893.7474189815, where the 40893 is the
number of days since December 30, 1899, and the .7474189815 is the fraction
of 24 hours that is 5:56:17 PM.

The zero date for SQL Server depends upon the datatype of the field. For
"smalldatetime", it's January 1, 1900. For "datetime", it's January 1, 1753.

So, even though you store just a time value, the integer portion is 0, which
is seen as a date for both ACCESS and SQL Server.
 
DateTime fields in SQL Server (and in ACCESS, for that matter) store a date
and time always. This is because dates/times are stored as decimal numbers,
where the integer portion represents the number of days since a fixed date
(differs for ACCESS and SQL Server) and the decimal (fraction) portion
represents the fraction of "time" within a 24-hour period (midnight to
midnight).

So for example, in ACCESS, the datetime value of 12/16/2011 5:56:17 PM
(December 16) is actually stored as 40893.7474189815, where the 40893 is the
number of days since December 30, 1899, and the .7474189815 is the fraction
of 24 hours that is 5:56:17 PM.

The zero date for SQL Server depends upon the datatype of the field. For
"smalldatetime", it's January 1, 1900. For "datetime", it's January 1, 1753.

So, even though you store just a time value, the integer portion is 0, which
is seen as a date for both ACCESS and SQL Server.
--

        Ken Snellhttp://www.accessmvp.com/KDSnell/













- Show quoted text -
Click to expand...

Thanks for helping,
I am ableto store only time invformation wen I insert into MS Access
and MS SQL server and update.
The value does not have time.
Only I update using VBA code that when I update SQL server table it
shows 12-15-2011 11:43:00 PM which is today's date.
If it store whole number as zero then should show 1899-12-31 11:43:00
PM.
My code does subtract the whole number which should store decimal part
of the date time value

thanks again,


iccsi
 
I don't know what the SQL Server default is... that may be the difference...
the "zero date" for Access, if I recall correctly, is Dec. 30, 1899, not
Dec. 31, 1899 (don't ask me why!).

If you are, in fact, working with Access date/time variables or fields, try
using the builtin functions... check help on Date and Time functions, and
see what result you obtain.

Larry Linson
Microsoft Office Access MVP


DateTime fields in SQL Server (and in ACCESS, for that matter) store a
date
and time always. This is because dates/times are stored as decimal
numbers,
where the integer portion represents the number of days since a fixed date
(differs for ACCESS and SQL Server) and the decimal (fraction) portion
represents the fraction of "time" within a 24-hour period (midnight to
midnight).

So for example, in ACCESS, the datetime value of 12/16/2011 5:56:17 PM
(December 16) is actually stored as 40893.7474189815, where the 40893 is
the
number of days since December 30, 1899, and the .7474189815 is the
fraction
of 24 hours that is 5:56:17 PM.

The zero date for SQL Server depends upon the datatype of the field. For
"smalldatetime", it's January 1, 1900. For "datetime", it's January 1,
1753.

So, even though you store just a time value, the integer portion is 0,
which
is seen as a date for both ACCESS and SQL Server.
--

Ken Snellhttp://www.accessmvp.com/KDSnell/













- Show quoted text -
Click to expand...

Thanks for helping,
I am ableto store only time invformation wen I insert into MS Access
and MS SQL server and update.
The value does not have time.
Only I update using VBA code that when I update SQL server table it
shows 12-15-2011 11:43:00 PM which is today's date.
If it store whole number as zero then should show 1899-12-31 11:43:00
PM.
My code does subtract the whole number which should store decimal part
of the date time value

thanks again,


iccsi
 
I don't know what the SQL Server default is... that may be the difference...
the "zero date" for Access, if I recall correctly, is Dec. 30, 1899, not
Dec. 31, 1899 (don't ask me why!).
Click to expand...

Because the initial release of Lotus 123 mistakenly treated 1900 as a leap
year, so its day basis had to be moved back a day to give correct dates.
Microsoft adopted the Lotus date standard for compatibility with their (then
dominant) competitor.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Wow! Living history!

John W. Vinson said:
Because the initial release of Lotus 123 mistakenly treated 1900 as a leap
year, so its day basis had to be moved back a day to give correct dates.
Microsoft adopted the Lotus date standard for compatibility with their
(then
dominant) competitor.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
Click to expand...
 
Sort of like you, Larry! <g, d & r>

"Access Developer" wrote in message
Wow! Living history!
 
Douglas J Steele said:
Sort of like you, Larry! <g, d & r>

"Access Developer" wrote
Click to expand...

Hmmm. I may have been one of the last people to purchase a brand, new copy
of VisiCalc. My then-spouse was taking a college business course, and the
course requirements included keeping a running log of the projects using a
particular format in VisiCalc. So I dutifully acquired and installed
VisiCalc. (Fortunately, it was already so out-of-date that it was
inexpensive.) Then when the course actually began, it turned out they no
longer used VisiCalc but had simply not updated the material describing the
course.

Also, around 1990, I was in National Tech Support for "a major computer
manufacturer" and was secondary national support person for Lotus 1-2-3 M
(did you realize there had been a mainframe version of 1-2-3?). To assist
our technical support ("Systems Engineers") in the branch offices, I created
a comprehensive list of available spreadsheet software, and how, if
possible, the spreadsheets could be converted to something that Lotus 1-2-3
M would accept. There were over 50 spreadsheets in use and documented in the
literature of the day, and the most common "lingua franca" was dBase 3.

Of course, Lotus 1-2-3 M and the vast majority of those 50+ spreadsheets are
now just footnotes in computer history. But, just for history's sake, I wish
I had a copy of that paper.. (As am I, of course.) I retired from "a major
computer manufacturer" in 1991 and started hanging out with a bunch of
database neer-do-wells in 1993.

But, I had not been aware of the date calculation and how it affected
Microsoft software.

Larry
 
Back
Top