Inconsistent results in writing string dates to a local variable

[Gordon Hughes]

I am trying to generate string dates in ISO 8601 format to feed to a program that downloads data from an external database. I have two versions of the code which generate inconsistent results. First

local start_date=td(01apr2024)
local end_date=td(15apr2024)
forval date=`start_date'/`end_date' {
     local xdate: display %tdCYND `date'
     display "date: " %td `date' " xdate: " `xdate'
}

generates results like this:

date: 01apr2024  xdate: 20240401
date: 02apr2024  xdate: 20240402
date: 03apr2024  xdate: 20240403
date: 04apr2024  xdate: 20240404

which is as expected. But now a slight modification in the date display format

local start_date=td(01apr2024)
local end_date=td(15apr2024)
forval date=`start_date'/`end_date' {
     local xdate: display %tdCY-N-D `date'
     display "date: " %td `date' " xdate: " `xdate'
}

generates results as follows:

date: 01apr2024  xdate: 2019
date: 02apr2024  xdate: 2018
date: 03apr2024  xdate: 2017
date: 04apr2024  xdate: 2016

i.e. the values of the local variable are interpreted as year-month-day.

This is weird! Can anyone explain what is going on? In other circumstances the format %tdCY-N-D works properly to generate an ISO 8601 formatted string date.

Finally any other ideas on how I can get the local variable xdate to hold the string date "2024-04-01"?

[Andrew Musau]

Note sure that I follow what your question is, but this may help:

local xdate= string(td(01apr2024), "%tdCY-N-D")
display "`xdate'"

Res.:

. local xdate= string(td(01apr2024), "%tdCY-N-D")

. 
. display "`xdate'"
2024-04-01

[Andrew Musau]

OK, I think I see your issue in #1. Look at the double quotes or lack there of in the example below. In the first instance, Stata evaluates the dashes as minus signs and does the computation \(2024-4-1 = 2019\).

local xdate: display %tdCY-N-D td(01apr2024)
di `xdate'
di "`xdate'"

Res.:

. local xdate: display %tdCY-N-D td(01apr2024)

. di `xdate'
2019

. di "`xdate'"
2024-04-01

[Gordon Hughes]

I tried a variant of that but the key issue is that date is a local loop variable, so in your code td('date') is interpreted as td(23467) which is invalid, while using `date' in place of td(01apr2024) generates the same results as in my second case.

The crucial issue is the order in which local variables are interpreted. What is happening is that we are not getting "2024-04-01" but "2024"-"04"-"01", i.e. the arithmetic is being performed before the string conversion. I can sort of understand the logic but it is certainly not consistent with datetime formatting.

Hence, I am still stuck with trying to generate local string variables inside a loop which contain the string "2024-04-01" on the first iteration and then "2024-04-02" on the second iteration, etc. Of course, I can write special cases but it is the general case that I can't get.

[Andrew Musau]

As I remarked in #3, the issue is to do with the quotes.

local start_date=td(01apr2024)
local end_date=td(15apr2024)
forval date=`start_date'/`end_date' {
     local xdate: display %tdCY-N-D `date'
     display "date: " %td `date'  `" xdate:  `xdate' "'
}

Res.:

. 
. forval date=`start_date'/`end_date' {
  2. 
.      local xdate: display %tdCY-N-D `date'
  3. 
.      display "date: " %td `date'  `" xdate:  `xdate' "'
  4. 
. }
date: 01apr2024 xdate:  2024-04-01 
date: 02apr2024 xdate:  2024-04-02 
date: 03apr2024 xdate:  2024-04-03 
date: 04apr2024 xdate:  2024-04-04 
date: 05apr2024 xdate:  2024-04-05 
date: 06apr2024 xdate:  2024-04-06 
date: 07apr2024 xdate:  2024-04-07 
date: 08apr2024 xdate:  2024-04-08 
date: 09apr2024 xdate:  2024-04-09 
date: 10apr2024 xdate:  2024-04-10 
date: 11apr2024 xdate:  2024-04-11 
date: 12apr2024 xdate:  2024-04-12 
date: 13apr2024 xdate:  2024-04-13 
date: 14apr2024 xdate:  2024-04-14 
date: 15apr2024 xdate:  2024-04-15

[Gordon Hughes]

Many thanks for the suggestion! That works and it is not even necessary to embed the double quoted " xdate: `xdate' " in single quotes, but the order of the quotes is critical to how the statement is parsed.

Another lesson is that my example was misleading me. The single quoted `xdate' was all I needed for the API call to the database.