FGDB SQL — Use CONCAT to concatenate string and date/number without casting

[Bud]

Edited. The previous title was "FGDB SQL function to convert date to string (or concatenate date)."

ArcGIS Pro 3.2.1

Regarding file geodatabase SQL: SQL for reporting and analysis on file geodatabases

There are times when I want to include a date in a string. But the CONCAT() function can't seem to handle dates. 

For example, this works in a FGDB database view:

select
    objectid,
    concat(t_species, t_species) as concat
from
    species_records

But this doesn't:

select
    objectid,
    concat(t_species, t_date) as concat
from
    species_records

ERROR 160195: An invalid SQL statement was used.

Could the CONCAT() function be enhanced so that it accepts dates?

[MarceloMarques]

My 2 cents. Certain SQL operations are only available in a RDBMS (Oracle, SQL Server, PortgreSQL) because the SQL Engine behind the RDBMS that is quite complex. Certain SQL functions in a File Geodatabase might not be available and it will be difficult to implement that because of this reason. Therefore, the best aproach is move the data from the File Geodatabase to an Enterprise Geodatabase, even Mobile Geodatabase will present some limitations as well, thus move the data to an Enterprise Database (Oracle, SQL Server, PostgreSQL). I hope this helps.

[Bud]

Related:

• Comments in an unrelated post about the CAST() function in FGDB SQL
• Esri Case 03529800: FGDB database view: Can't use CAST function in WHERE clause

Edit: I think this idea still applies, despite the existence of the CAST function. The CONCAT function should be able to automatically convert dates and numbers to strings when concatenating a string and a number/date.