Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


From   Nick Cox <[email protected]>
To   [email protected]
Subject   Re: st: Stata dates -tostring- cannot be converted reversibly; no replace
Date   Mon, 21 Jan 2013 11:31:45 +0000

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 <[email protected]> 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/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index