st: Stata dates -tostring- cannot be converted reversibly; no replace

[Tim Evans <Tim.Evans@wmciu.nhs.uk>]

Hi all,

I usually query data in SQL Server and copy and paste into Stata, however my local settings are not happy with me copy and pasting +500,000 rows of data into a Stata editor as I don't have enough memory available. To get around this, I connect Stata to my SQL server database using the ODBC load which is fine, however I'm having trouble with dates. In the copy paste direct from SQL, my dates go in as string variables and I can convert them into Stata dates using something like this:

generate double diagdate2 = date(dx, "YMDhms")
format %td diagdate2

Which gives my data a numeric value and applies some sort of variable label. I'm familiar with how to substract/add extra years onto these data. However, when I load the data through ODBC, my previous string dates are imported as doubles with the format %tc and look like this:

15oct1999 00:00:00

When I try to convert to %td the above date looks like this:

1.26e+12

I thought that if I converted the date to a string variable using -tostring- this would allow me to repeat my previous code, however when I run this code:

tostring  dx, gen(dx2)

I have the error message
dx cannot be converted reversibly; no generate

so I tried:
tostring  dx, gen(dx2) force

this gives a string value, but it looks like this:
1.25556e+12

I'm using Stata 11.2, I've tried to search for a solution to get my data into numeric format so that I can convert to a sensible date format. I'm after a way of turning the ODBC loaded dates into strings so that I can repeat my original code/set-up or a way of directly converting to a number and applying the %td format. Any advise appreciated.

Best wishes

Tim
_DISCLAIMER:
This email and any attachments hereto contains proprietary information, some or all of which may be confidential or legally privileged. It is for the exclusive use of the intended recipient(s) only. If an addressing or transmission error has misdirected this e-mail and you are not the intended recipient(s), please notify the author by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this e-mail or any attachments, as this may be unlawful.


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/