DateTime Pattern Guidelnes

Email and File type downloads use dates in the filename or, in the case of some emails, the subject field for in order to provide a date to identify the time period for which uploaded table data is relevant.

Switchboard can use a matching pattern to determine the date from the given text, for example if the file names are created with a date string such as mylogfile-12-25-22.csv to signify that the data contained therein is from the 25th of December, 2022.

The following common properties used for these download types.

datetime_pattern

Used in both file and email downloaders when the date is contained in the file name.

subject_datetime_pattern:

Used only in email downloads when the date is sourced from the email’s subject field.

delivery_delay_days

Used when date is not present in the file or subject, the value of this field defines the delta in days to be applied to the delivery date. If delivery_delay_days is ‘1’, the table date will be set to the previous date. For example, if an email is sent on 7/16/23 with a delivery delay date of 1, then the date used for the data in the table name will be 7/15/2023.

Date Format Variables and Special Characters

The properties used assigning a date to data use specific characters to match to time periods within a date string.

For instance, the pattern YYYYMMDD corresponds to an 8 digit date including year month and date, such as 20230717 (that is, the 17th of July, 2023).

Since dates can be provided in several different formats, below are the codes to use in your SBS to ensure SB processes each pattern correctly.

Literal characters and wildcards should be used to specify and potentially differentiate between date and non date numbers or the correct date to use vs dates not to use.

The asterisk character * can be used as a wildcard to specify anything before or after the date pattern.

MM can be used to specify Full and abbreviated month names as well as 2 digit month numbers

M specifies month where no leading 0 is used

DD can be used to specify two digit date

D specifies Date where no leading 0 is used

YYYY specifies 4 digit year

YY specifies 2 digit year

HH can be used to specify hour

H specifies hour when no leading 0 is used

In addition to these characters, literal characters such as the _ (underscore) or - (dash) should also be used to match the formatting of the date is provided. This can even include the file extension should the date occur at the end of the file name.

As an example, for a report named like:

Data_Source_Programmatic_Daily_Report_2023-06-12.csv

The datetime pattern would be *_YYYY-MM-DD.csv.