Possible fault in date formatting

I have a database with a date field (Date) and a text field (Names). I create an array with additional dates and names, separated by a tab, and I add this array to the database.

I won’t always know the format of the date, so I determine this with a SourceDateOutputPattern = getdictionaryvalue(FieldProperties,"OUTPUTPATTERN") statement and then create the array with a statement like this in a loop:

AllCombosArray = AllCombosArray +cr()+ datepattern(array(FieldsArray,I,cr()),SourceDateOutputPattern) +tab()+ array(CategoryArray,J,cr())

If the output pattern is of the form “dd mm yyyy” or “dd MM yyyy”, the array is imported correctly.

If, however, the format is “dd Mon yyyy” or “dd Month yyyy”, the date field fills with blanks.

Can anybody replicate this behaviour?

There are lots of output patterns that Panorama won’t accept as input formats for dates. I would suggest just using YYYY.MM.DD as the pattern, Panorama will always accept dates in that format no matter what the system date settings are.

That said, I don’t understand what your code is doing. You say that you “create an array with additional dates and name”, I presume you mean a text array, which means that the dates are already stored in some text format. The datepattern( function normally requires a number as the first parameter, but you are giving it text. Ok, I checked the code for datepattern(, and it will accept a numeric value in text format, for example 2457933 (today’s date), is that what you have put in your “array of dates”? It definitely won’t accept something like 3/5/17 or any other human readable form of date.

But I’m adding records to existing data whose date format may differ from this.

Well it definitely does on my iMac. These are the last few records of my database before importing (I’ve added some empty records):

The first field is a date field, as shown:

I then create an array using this statement:

AllCombosArray = AllCombosArray +cr()+ datepattern(array(FieldsArray,I,cr()),SourceDateOutputPattern) +tab()+ array(CategoryArray,J,cr())

in which the datepattern( function clearly creates data elements in the form, “1 1 2017”. I then import the array with:

openfile "+@AllCombosArray"

and the database then looks like this:

where the records with empty values in the dollar field are the imports. So maybe your machine “definitely won’t accept something like 3/5/17” but mine sure does.

Can anybody else make this happen?

What is FieldsArray? What does it contain? How did you fill it?

This procedure puts “1 01 0” in the A field.

Local x
x = "3/5/17"
A = datepattern(x,"mm DD yyyy")

FieldsArray contains a set of dates in numeric notation:

2457755
2457786
2457814
2457845
2457875
2457906
2457936
2457967
2457998
2458028
2458059
2458089

This is the code that created it:

;  If the top field is a date, determine the low and high dates and compile an array of dates to be used for column headings

if DateMode > ""
    field (CatFields)  sortup
    firstrecord
        execute {LowField = month1st(} + chevronquoted(CatFields) + {)}
    lastrecord
        execute {HighField = month1st(monthmath(} + chevronquoted(CatFields) + {,1)) - 1}    
    removesummaries 7
    
    if DateMode = "Year"
        HighField = year1st(monthmath(HighField,12)) - 2
    endif
    
    ; Compile an array of all possible dates between low and high dates
    
    FieldsArray = LowField
    NewDate = LowField
    

    loop
        case DateMode = "Month"
            NewDate = monthmath(NewDate,1)
        case DateMode = "Quarter"
            NewDate = monthmath(NewDate,3)
        case DateMode = "Year"
            NewDate = monthmath(NewDate,12)
        endcase
        
        stoploopif NewDate > HighField
        FieldsArray = FieldsArray + cr() + NewDate
    while forever    
else

where CatFields is a variable which contains the name of the date field, «Date of transaction», the first field in the screenshots in my earlier posts.

Then that confirms what Jim was saying. The datepattern( function isn’t going to accept something like “3/5/17” as its first parameter. It will however accept something like “2457933”. I think it is probably just applying the val( function when you give it text. Val(“3/5/17”) is 3. That’s too early a date for the datepattern( function.

The answer to your original question was in his first paragraph. There are a limited number of patterns a date field will accept as input. YYYY.MM.DD works in every region, so he suggested you use that.

The first parameter of the datepattern( function should be numeric - yours is text. That’s why you get “1 01 0”. Interestingly, Panorama X doesn’t reject the text parameter as invalid, which it should. Maybe there are other problems with the function?

This code:

local a
a = datepattern(2458000,"dd mm yyyy")
openfile "+@a"

adds the date 3 09 2017 to the date field of my database. Can you replicate that?

Putting the 2458000 parameter in quotes gives the same result - interesting.

Panorama X rarely gives you the “Type Mismatch [a number/text] was encountered when [text/a number] was expected” error. It now just uses the val( or str( function to convert what it got to what it wants. Using val( on “3/5/17” is unlikely to produce the number you intended though.

Putting it in a text array is pretty much the same as putting in quotes. Either way, you are giving it text when it expects a number.

We seem to be going around in circles here - maybe I’m not keeping up but it seems to me that I’m feeding a text value {datepattern(array(FieldsArray,I,cr()),SourceDateOutputPattern)} into the date field and it’s coming out correctly.

And why does this work?

local a
a = datepattern(2458000,"dd mm yyyy")
openfile "+@a"

This is all related to my Crosstabs emulator which is now finished. I’ll be calling for volunteers to test it shortly and maybe you can check out the code then.

It works because it was designed to work that way. Dates are numbers. The datepattern( function expects its first parameter to be a number, and you gave it one. Furthermore, it was the correct number for the date you had in mind.

The pattern you chose is one that a date field will accept as input in your region. It wouldn’t work for me in my region. Actually, it would accept it for that particular number, but it would be entered as September 3, not March 9.

If you were to use YYYY.MM.DD as the pattern instead, you wouldn’t need to know that I am in America, because that same pattern would just automatically work for us both.

You are feeding it a text value, and it works because the value is one that the val( function will convert to the correct number. Val(“2458000”) will return 2458000. Val(“3/5/17”) will return 3.

In Jim’s reply, he said that the datepattern( function wouldn’t work with text unless it was something like “2457933”, and asked you if that was the sort of thing you had in your array. You implied that he was wrong and gave an example that told us everything except the answer to his question. You didn’t say what that first parameter looked like. That’s why I asked what FieldsArray was. That was the first parameter.

I’m with you. I’m not looking for an argument here, I’m looking for the best way to add my new records. My interpretation of what you’ve said suggests that this should work:

local a
a = 2458000
openfile “+@a”

But it doesn’t - I get an empty date field. How would you add 100 new records?

This doesn’t work either:

local a
a = "2458000"
openfile "+@a"

You are misinterpreting. I haven’t been talking about the openfile statement at all. I’ve been talking about this one.

AllCombosArray = AllCombosArray +cr()+ datepattern(array(FieldsArray,I,cr()),SourceDateOutputPattern) +tab()+ array(CategoryArray,J,cr())

The datepattern( function in that statement uses

array(FieldsArray,I,cr())

as its first parameter. An array( function returns text. The datepattern( function expects a number. Jim expressed concern about that in his second paragraph. He said that text wouldn’t work for that parameter unless it were something like “2457933”, and asked if that was how the dates were formatted in your “array of dates”. He didn’t specifically state that he was talking about FieldsArray, but he did specifically say that he was talking about the first parameter of your datepattern( function. Nothing in his second paragraph had anything to do with the openfile command.