FGDB database view — Casting date as text in SELECT clause results in blank field

Bud

ArcGIS Pro 3.2.1; file geodatabase

I can create this FGDB database view without errors:

select 
  s.*,
  cast(t_date as char(50)) as test_col
from 
  species_records s

However, the field that uses CAST is blank in the attribute table. That's not expected.

I don't have that problem if I remove s.* and replace it with the fields listed one by one:

select 
  objectid,
  t_unique_id,
  t_species,
  t_common_name,
  t_taxon_group,
  t_threat,
  t_grid_refer,
  t_site,
  t_date,
  cast(t_date as char(50)) as test_col
from 
  species_records s

 

And I don't have the s.* problem in a mobile geodatabase view (although the date is displayed as a number, not a textual date, but maybe that's expected for SQLite date fields):

select 
  s.*,
  cast(t_date as char(50)) as test_col
from 
  species_records s

 

Question:

Why does casting a date field in the SELECT clause result in a blank column — when the query uses s.* ?

 

Robert_LeClair

A file-based data including file geodatabases and all content in it that use standardized queries use the ArcGIS SQL dialect that supports a subset of SQL capabilities. In other words, it has limitations on the SQL expressions that are available in a Data Base Management System such as Oracle or SQL Server. On the other hand, databases or enterprise geodatabases use the SQL syntax of the underlying RDBMS, such as, Oracle, SQL Server, PostgreSQL, SAP HANA, and IBM Db2, where each database uses its own slightly different SQL dialect.

So it seems this is known limitation of using the s.* in a database view for a file geodatabase.