converting date format from letters to numeric and adding a separator between month day year

[eric boorman]

I currently have date variable (lets call it date1 for example). I was able to format it as a date and it appears as follows.

gen date1 = date0
format date1 %td

01jan2000

I want dates to appear like this with the month appearing in two digit numbers instead of words. I was also wanting to us / or some other symbol (e.g., - _) to separate the month day and year.

gen date1 = date0
format date1 %td

01/01/2000

[Nick Cox]

I work out a display format by looking at

help datetime display formats

and fooling around with display and specific examples where the desired result can be checked. So,

. di %tdDD/NN/CCYY mdy(1,1,2000)
01/01/2000

. di %tdDD/NN/CCYY mdy(8,29,2024)
29/08/2024

[eric boorman]

I found the necessary formatting information online, however whenever I apply this, the format doesn't change.

* date0 appears as 01jan2000
gen date1 = date0
format date1 %tdNN/DD/CCYY
tab date1

01jan2000

I thought I might need to do two different format statements like this but this does not work either. The format is unchanged.

* date0 appears as 01jan2000
gen date1 = date0
format date1 %tdDDMonCCYY 
format date1 %tdNN/DD/CCYY
tab date1

01jan2000

I also thought of turning the date back into a string and then trying to create a new variable but this does not appear to be working either.

* date0 appears as 01jan2000
tostring date0, replace
gen date1 = date0
format date1 %tdDDMonCCYY 
format date1 %tdNN/DD/CCYY
tab date1

01jan2000

Once a variable is in a date format, how do we change from one format to another?

[Clyde Schechter]

The problem seems to be with the -tab- command, not with the date variable formatting. In fact, it looks to me like you have uncovered a bug in -tab- and should inform Stata Technical Support.

. clear*

. set obs 1
Number of observations (_N) was 0, now 1.

. gen date0 = td(01jan2000)

. format date0 %td

.
. list

     +-----------+
     |     date0 |
     |-----------|
  1. | 01jan2000 |
     +-----------+

.
. gen date1 = date0

. format date1 %tdNN/DD/CCYY

. tab date1

      date1 |      Freq.     Percent        Cum.
------------+-----------------------------------
  01jan2000 |          1      100.00      100.00
------------+-----------------------------------
      Total |          1      100.00

. list

     +------------------------+
     |     date0        date1 |
     |------------------------|
  1. | 01jan2000   01/01/2000 |
     +------------------------+

.
. tostring date1, format(%tdNN/DD/CCYY) gen(date2) force
date2 generated as str10
date2 was forced to string; some loss of information

. tab date2

      date1 |      Freq.     Percent        Cum.
------------+-----------------------------------
 01/01/2000 |          1      100.00      100.00
------------+-----------------------------------
      Total |          1      100.00

.

The part shown in blue demonstrates, via the -list- command (or you could use -browse- there) that internally Stata has adopted the mm/dd/yyyy display formatting you want. But for some reason, -tab- does not respect it.

The subsequent code that -destring-s to a new variable date2 provides a workaround, similar to what you attempted, but with implementation details corrected.

[Nick Cox]

Several confusions here. One confusion is mine: I can't tell for sure what is in your date0. Please show the result of

describe date0 
dataex date0

Otherwise:

0. You're right about tabulate seemingly not respecting changes in daily date display format. I don't think I noticed that before.

1. Daily date display formats can only be assigned to numeric variables in principle and in practice that's only useful for numeric variables containing integers that really represent daily dates. That is, they respect Stata's rule that numeric daily dates are counted from 0 = 1 January 1960.

Here I create what I think is a daily date variable, but it's just integers to Stata until I assign a daily date display format. Then if I want a different daily date format I just issue a new format statement.

. clear

. set obs 3 
Number of observations (_N) was 0, now 3.

. gen testdate = cond(_n == 1, mdy(1,1,1960), cond(_n == 2, mdy(1,1,2000), mdy(8,29, 2024)))

. 
. list 

     +----------+
     | testdate |
     |----------|
  1. |        0 |
  2. |    14610 |
  3. |    23617 |
     +----------+

. 
. format testdate %td 

. 
. list 

     +-----------+
     |  testdate |
     |-----------|
  1. | 01jan1960 |
  2. | 01jan2000 |
  3. | 29aug2024 |
     +-----------+

. 
. format testdate %tdDD/NN/CCYY 

. 
. list 

     +------------+
     |   testdate |
     |------------|
  1. | 01/01/1960 |
  2. | 01/01/2000 |
  3. | 29/08/2024 |
     +------------+

2. Here's a question you have not yet asked, but somehow I guess that it may be lurking. Sometimes we see questions like How do I change the date format? when what people really (should) mean is How do I change from one date type to another?

Here's the general principle. DIsplay formats are just that; they affect what is displayed; they have no effect on what is stored.

So, suppose I want monthly dates.

format testdate %tm 

list 

     +----------+
     | testdate |
     |----------|
  1. |   1960m1 |
  2. |   3177m7 |
  3. |   3928m2 |
     +----------+

As in some folk tales, my punishment is that I got what I asked for.

0 as a monthly date is January 1960; so far, so good; but 14610 is as a monthly date July 3177, which is not what I wanted.

To convert from one kind of date to another, you need a conversion function.

. format testdate %td

. gen mdate = mofd(testdate)

. format mdate %tm

. list

     +--------------------+
     |  testdate    mdate |
     |--------------------|
  1. | 01jan1960   1960m1 |
  2. | 01jan2000   2000m1 |
  3. | 29aug2024   2024m8 |
     +--------------------+

Hope that helps!