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

[Nick Cox <njcoxstata@gmail.com>]

Yes; it is 2013.

On Mon, Jan 21, 2013 at 12:18 PM, Nick Cox <njcoxstata@gmail.com> wrote:
> OK.
>
> It's 21 Jan 2012 today, all day until midnight. By the same logic
> wrapping your argument in -floor()- is indicated as you want an
> integer result and also to round down.
>
> Using -dofc()- would be more direct, however.
>
> Nick
>
> On Mon, Jan 21, 2013 at 12:09 PM, Tim Evans <Tim.Evans@wmciu.nhs.uk> wrote:
>> Hi Nick,
>>
>> Thanks for your comments. I realise that I need to convert from milliseconds to days.
>>
>> This code now works for me:
>>
>> gen death =  date_of_death/(1000*60*60*24)
>> format %td death
>>
>> My organisation isn't a subscriber to the Stata journal - I'll read your reference in a years time (or whenever its freely available)!!!
>>
>> Best wishes
>>
>> Tim
>>
>>
>>
>>
>> -----Original Message-----
>> From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Nick Cox
>> Sent: 21 January 2013 11:32
>> To: statalist@hsphsun2.harvard.edu
>> Subject: Re: st: Stata dates -tostring- cannot be converted reversibly; no replace
>>
>> There are various confusions here.
>>
>> 1. -tostring- is essentially a wrapper for -string()-, but the function -string()- has two arguments, the second of which is optional. The second argument is a numeric format, which has a default; otherwise it would not be optional.
>>
>> -tostring- is telling you that what you are asking for is a bad idea as operations such as
>>
>> real("1.26e+12")
>>
>> which would be the inverse of the operations you are implying such as
>>
>> string(1255564800000)
>>
>> are not guaranteed to give you back the original. -tostring- doesn't try to read your mind; that would imply knowing somehow the format that you would have supplied but did not.
>>
>> So, as far as -tostring- is concerned you need to specify an option
>> -format()- or -usedisplayformat-, as is documented in the help.
>>
>> 2. However, I see no reason here for trying to convert your date variables to string, so -tostring- is irrelevant here.
>>
>> 3. The most important confusion here is the idea that changing the format of a variable somehow changes its values. Not so. A date-time is a time in milliseconds, and a date-time for some date in 1999 is a number in trillions; if you tell Stata that that is a daily date, the same number is a date billions of years hence and Stata gives up on showing it as such.
>>
>> . di %23.0f clock("15oct1999 00:00:00", "DMY hms")
>>           1255564800000
>>
>> . di %td   1255564800000
>>  1.26e+12
>>
>> Further simple examples of the same point
>>
>> . clear
>>
>> . set obs 1
>> obs was 0, now 1
>>
>> . gen time = 1
>>
>> . format time %td
>>
>> . l
>>
>>      +-----------+
>>      |      time |
>>      |-----------|
>>   1. | 02jan1960 |
>>      +-----------+
>>
>> . format time %tc
>>
>> . l
>>
>>      +--------------------+
>>      |               time |
>>      |--------------------|
>>   1. | 01jan1960 00:00:00 |
>>      +--------------------+
>>
>> . format time %tm
>>
>> . l
>>
>>      +--------+
>>      |   time |
>>      |--------|
>>   1. | 1960m2 |
>>      +--------+
>>
>> In all these format changes, the value remains 1; what changes is how that value is displayed.
>>
>> In order to convert dates from one kind to another you need a conversion function, not a format change. See -help dates and times-
>>
>> See also
>>
>> Cox, N.J. 2012.
>> Stata tip 113: Changing a variable's format: What it does and does not mean Stata Journal 12(4): 761-764
>>
>>
>> On Mon, Jan 21, 2013 at 10:58 AM, Tim Evans <Tim.Evans@wmciu.nhs.uk> wrote:
>>
>>> 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.

*
*   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/