Archiving Statements

select * from dms:sysobject where sysitemcreationdate <= datetime'2018-01-23 13:14:15'

This will archive all objects (of any document type) that were created before or exactly on January 23, 2018, 13:14:15.

select * from dms:sysobject where syscreationdate < datetime'2018-07'

This will archive all objects (of any document type) that were created and last modified before July 1, 2018, 00:00:00.

select * from dms:document d where d.customfield1 = 'acknowledged' and d.customfield2 < datetime'2018-02-01'

This will archive all objects of the type document where customfield1 has the value acknowledged and customfield2 contains a date that is January 31, 2018, 23:59:59.999 or earlier.

select * from dms:sysobject where sysitemcreationdate <= dateadd(day, -7, currentdatetime())

This will archive all objects (of any document type) that were created a week ago from now or earlier.
The dateadd function can add or subtract a specified amount of time from the provided date: dateadd(<unit>, <amount>, <date>)
The unit can be "second", "minute", "hour", "day", "week", "month", "year"
The date can be taken from the index data, specified manually or currentdate() (start of current day)/currentdatetime() (now))

select d from dms:sysobject d join ecm:syscreationtraceowner2user c2u join org:sysuser u
where u.sysname = 'user4711'

This will archive all objects (of any document type) of which user4711 was the creator of the currently active version.
This means either the object is in version 1 and user4711 was the creator, or the version is in version 2 or higher and user4711 was the last modifier.

select * from dms:sysobject (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u
where u.sysname = 'user4711'

This will archive all objects (of any document type) of which user4711 was the creator or the modifier of any version.

select * from dms:sysobject (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u
where d.sysversionnumber = 1 AND u.sysname = 'user4711'

This will archive all objects (of any document type) of which user4711 was the the creator of the first version.
This means user4711 created the (first version of the) item.

select * from dms:document where sysitemid = id'ED01B5E5996648088D0A474C37962C93'

This will archive the DMS object of type document with the the item ID ED01B5E5996648088D0A474C37962C93.

select * from dms:document where longnumber = 123456789L

This will archive all DMS objects of type document where the field longnumber has the value 123456789.

select * from dms:document where string1 = 'ipsum'

This will archive all DMS objects of type document where the field string1 has the value ipsum.

select * from dms:document where catalogfield = 'english'

This will archive all DMS objects of type document where the field catalogfield has a catalog entry with data value english.

select * from dms:document where orderstable containsrowwithcondition(orderstablerecord.order = 'order4711')

This will archive all DMS objects of type document where the table orderstable has a row where the column order has the value order4711.
This also applies to multi-tagging fields which are technically a table with one column. In this case, any element has to have the value order4711. ← Valid for versions <= 8.16 LTS

select * from dms:document where ordersset containsrowwithcondition(order = 'order4711')

select * from dms:document where ordersset containsrowwithcondition(order IN ('order4711','value0815'))

Valid for versions >= 9.0

This will archive all DMS objects of type document where the field of type SET[string] (multi-tagging) with the technical name ordersset contains an element (chip) with the technical element name  order that has the value order4711 (first example) or the values order4711 or value0815 (second example).