The database management software that I'm using stores dates in a Date/Time
format, which is eight bytes.
Stata is able to load the date portion of the value via -odbc load- from the
database without any difficulty (automatically displaying the value in the
%td format). And Stata even loads the values as a type -double-
(eight-byte) datatype.
But Stata (or the ODBC driver) truncates (sets-to-zero) the four bytes
containing the information about the time portion of the Date/Time value, so
the date is kept but the time is lost during retrieval from the database.
Is there a workaround for this?
For example, can I instruct Stata to retrieve the values as type -double-,
disregarding the datatype (display type) specified by the database? If so,
then once I get the complete eight-byte value into Stata as a type -double-
variable, I can easily do the arithmetic inside Stata to partition date and
time, and place the two four-byte pieces into separate type -long- integer
variables in order to retain all of the information.
Or would I need to do the work up-front inside the database, for example,
sending SQL statements via -odbc- to the database management system to
create a new column (in a query or view) and do the arithmetic there, if
it's possible, to copy the four bytes containing the time portion of the
Date/Time value into the new column and then pulling that into Stata via
ODBC?
If it matters, the database management system that I'm using at the moment
for training and exploratory purposes is Microsoft Access 2000, but if there
is a more general answer to Date/Time transfers via ODBC, that would be
helpful. I would assume that this issue would come up also with some
within-trading-day time-series databases used in finance and investment.
Joseph Coveney
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
