idea FGDB SQL — Use CONCAT to concatenate string and date/number without casting in ArcGIS Pro Ideas

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

Bud — Wed, 17 Jan 2024 05:14:49 GMT

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?

Re: FGDB SQL function to convert date to string (or concatenate date)

MarceloMarques — Sun, 14 Jan 2024 20:34:53 GMT

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.

Re: FGDB SQL function to convert date to string (or concatenate date)

Bud — Thu, 18 Jan 2024 06:25:04 GMT

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.