Feature Reader from Esri MGDB where clause based on datetime field

Have you got a sample feature or 2 that can be shared in an MGDB?

There isn’t a lot of documentation on datetime in MGDB, but the underlying technology is SQLite.  Interestingly, SQLite does not have a datetime data type, and platforms using it must choose between either a form of datetime text (usually ISO 8601) or a datetime number.   So looking at the data instead in a plain SQLite Reader (which will read the raw contents of an MGDB) should show exactly how ESRI are storing DateTime in an MGDB.

The MGDB Reader likely is not outputting the raw SQLite value but instead converting it to FME DateTime, so you are not “seeing” its true raw value, and it is the raw value that needs to be handled in the WHERE clause, hence we seeing it in a plain SQLite viewer/reader will give most of the answer.

Having had to also do datetime queries in SQLite, there are a likely 1 or 2 things going on here.  If it is in ISO format then often have to use a SQLite Datetime function like JulianDay() or strftime() to get the number into something that is numeric to get something to compare against.  Hopefully the MGDB Reader API supports SQLite functions inside the WHERE clause.

https://www.sqlite.org/lang_datefunc.html

Thanks @bwn and @todd_davis for both answers. Indeed the JULIANDAY formatting solved my issue and the syntax provided works in the Feature Reader where clause with MGDB.