Bookmark and Share

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

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

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

From   Tim Evans <>
To   "''" <>
Subject   RE: st: Stata dates -tostring- cannot be converted reversibly; no replace
Date   Mon, 21 Jan 2013 12:09:30 +0000

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


-----Original Message-----
From: [] On Behalf Of Nick Cox
Sent: 21 January 2013 11:32
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


which would be the inverse of the operations you are implying such as


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")

. di %td   1255564800000

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 <> 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:

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:

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