Can we append a DateTime to an array with the ArrayAppend() function of ColdFusion?

Question

ERROR:Can't cast Object type [DateTime] to a value of type [Array]

<cfset Seniority=ArrayNew(1)>
  <CFLOOP QUERY="all_employees">
      <cfif isNull(all_employee.TimeInPositionDate) >
          <cfset ArrayAppend(Seniority,all_employee.hiredate)>
      <cfelse>
          <cfset ArrayAppend(Seniority,all_employee.TimeInPositionDate)>
      </cfif>
  </CFLOOP>

Answer 1

I still think looping is much easier in CFScript (not to mention the fact that it just looks cleaner to me. Anyway, I can knock this down to essentially one line of code (beyond the array creation and looping code).

First, I set up my fake query object:

all_employees = QueryNew(
    "id, TimeInPositionDate, hiredate" ,
    "integer, date, date" ,
    [
        {
          id:1 ,
          TimeInPositionDate: "2018-01-01" , 
          hiredate: "2017-01-01"
        } , 
        {
          id:2 ,
          // TimeInPositionDate: Not defined, so NULL
          hiredate: "2017-02-01"

        } , 
        {
          id:3 ,
          TimeInPositionDate: "2018-03-01"
          //hiredate: Not defined, so NULL
         } ,
        {
          id:4 
          //TimeInPositionDate: Not defined, so NULL
          //hiredate: Not defined, so NULL
         } 
    ]
);

I have created 3 rows. 1 full row, 1 with just a TimeInPositionDate, 1 with just a hiredate and 1 with neither. ColdFusion has always been a little odd with how it treats null, and it doesn't always mesh up well with a SQL query. In our fake query, we just don't define the rows we want to be null.

Next I create my array:

Seniority = [] ;

We don't have to use ArrayNew() to make an array. We can just use implicit notation to be much shorter.

Next we use a for/in loop around our query to append row data to our new array.

for (r in all_employees) {
    Seniority.append( len(r.TimeInPositionDate) ? r.TimeInPositionDate : r.hiredate ) ;
}

Here, we're using the append() member function to add to our Seniority array. We also use a ternary operation to pick which value we want to add. It basically says that if there is a length to TimeInPositionDate for that row, then we use the first condition (TimeInPositionDate), otherwise we use hiredate.

I though about using an Elvis Operator...

Seniority2.append( q.TimeInPositionDate ?: q.hiredate ) ;

..., but since the query is likely returning q.TimeInPositionDate="" instead of an actual null value, then the first value is technically defined and gets chosen. That would work on a true null, but an empty string isn't a null.

You could probably also use each() or some other loopish function to iterate through your query object, but I've found for/in loops to usually be faster in cases like this. You'll have to test.

You can play with the full thing at:

https://cffiddle.org/app/file?filepath=de107907-234c-4df8-9386-02a288e53313/fdf21791-6cc4-4c55-b923-ad7726efa8ee/b3b6b906-8e3a-4f6b-9ec8-65845efc40f1.cfm

EDIT: NULLs can be so much fun. If we drop back into some Java, we can get an actual null value from the query rather than the cfquery-converted empty string, and show that Elvis is still in the building.

all_employees.getDate("TimeInPositionDate") ?: all_employees.getDate("hiredate") ) ;

Since Java's getDate() on a query will get the actual value and can handle a null, that correctly chooses the 2nd option for rows where TimeInPositionDate is null. That said, while I do appreciate the integration of Java and ColdFusion, I do not recommend it in this case. It's doing a lot of mixing of Java into the CF and isn't necessary beyond the demonstration.

Answer 2

Your issue stems form the variable lookup in CFML. While you're inside a query loop, ColdFusion will pull from the query scope before your variables scope. Due to there being a column in your query also called Seniority your code reads the same as ArrayAppend(all_employees.Seniority,all_employees.hiredate);

Changing the name of your array will solve the issue at hand.

 <cfset all_employees = queryNew(
    "Id,TimeInPositionDate,Hiredate,Seniority",
    "Integer,Timestamp,Timestamp,Timestamp",
    [
        {"Id":1,"HireDate":Now(),"Seniority":Now()},
        {"Id":2,"HireDate":Now(),"TimeInPositionDate":Now(),"Seniority":Now()}
    ]
)>

<cfset arrSeniority=ArrayNew(1)>

<CFLOOP QUERY="all_employees">
    <cfif isNull(all_employees.TimeInPositionDate) >
        <cfset ArrayAppend(arrSeniority,all_employees.hiredate)>
    <cfelse>
        <cfset ArrayAppend(arrSeniority,all_employees.TimeInPositionDate)>
    </cfif>
</CFLOOP>
<cfdump var="#arrSeniority#"/>