The other answers offer the preferred / logical / canonical solution.
There is a downside to those. Obtaining the
ORDER BY creationdate) for the tables with many rows now (think Stack Overflow) can take a while. None of the date fields in the SEDE database schema have an index.
Because SEDE is a bit special and we have access to implementation details we can (ab)use that knowledge to get the highest possible date without having the Query Engine perform a full Table Scan.
SQL Server keeps Meta data in its
sys schema for the different database objects. One of these meta data tables is
sys.tables which inherits the field
create_date from its parent, sys.objects.
Each Sunday the
sp_Refresh_Database.sql script is run for each database and its main function is to create a new database and recreate and populate all the tables. Once a new database is created, the tables are processeds by the script for example PostsWithDeleted, is created and populated with this statement:
Select * Into PostsWithDeleted
This means that the new table PostsWithDeleted gets created right before the last data could enter the database on SomeExportServer. Therefor there will not be any records in PostsWithDeleted after the
create_date of the PostWithDeleted table.
With this knowledge you can replace your
(select create_date from sys.tables where object_id =object_id('PostsWithDeleted'))
Do notice that you get the best results if you use the specific table you're using the dates from as that is the most accurate. On Stack Overflow there is a five hour gap between the first table and last table created. There are some data anomalies due to this effect. For example you can have suggestededits with a postid that is not present yet in the PostsWithDeleted table. The PostId does exist on the site.
If you want to have the latest date where all tables are most likely to be all in sync take the
create_date of the
Users table because that is always the first table that gets created anew each Sunday.
select create_date from sys.tables where object_id = object_id('Users')