DateTime

Let’s talk about date and time, which is always an issue - for many reasons.

What’s the problem with date and time?

There is no problem with date-only fields. The only challenge there is to take care of its format. Personally, I prefer yyyy-MM-dd because it is string-sortable. However, a source or target system may require different formats.

💡 A date-only field requires formatting, only.

Do use date-only fields whenever possible to avoid any confusion. For example: BirthDate, InvoiceDate, ContractDate etc. are date-only field that do not contain any time information.

Why does the time introduce problems?

Let’s talk about the time! I live in Germany and I remember well the afternoon of Dec. 31, 1999, when people in Australia were celebrating the new millennium. This means: time is worth nothing if you don’t know the location to which it refers. A time without a time-zone is not accurate.

💡 The ‘right’ time depends on the location! The time’s location information is called: [time-zone](https://en.wikipedia.org/wiki/Time_zone)!

Why the time-zone matters

Almost 20 years ago, I remember a difficult discussion with the Microsoft CRM team about date fields. At this time, Microsoft CRM did not support date-only fields and everything was stored in datetime fields.

The company I was working for, produced solar panels in the U.S. (HQ) and they sold these panels in Germany. When the invoice was printed in Germany, the invoice’s time was set to zero because we wanted date-only.

Then we got complaints from the U.S. team that there is something wrong with the invoice dates, and that the monthly invoice report is odd. First, we did not understand what happened, because looking at the database, everything seemed to be ok: InvoiceDate=2000-01-01 00:00.

What you must know is that the database server was located in Germany. Telling you this, I introduced the time’s location aka time-zone: Germany. When the user in the U.S. opened the invoice the Browser recognized the German server’s time-zone and it converted the datetime into Pacific time-zone (which is 9 hour behind Berlin [not taking daylight saving time into account]). Et voilà: the U.S. user saw an InvoiceDate=1999-12-31 15:00. Which is correct, isn’t it?

💡 When exchanging datetime information - this is what Boomi Integration does - you must take the time-zone into account. There is no way to disregard the time zone if you don't want to get into trouble. I promise!

Implicit or explicit

There are two ways to express a time-zone:

Explicit, by using a datetime format with time-zone information, or
Implicit, by agreement if the datetime does not contain time-zone information.

Explicit time-zone

💡 *The time zone using **UTC** is sometimes denoted ±00:00 or by the letter Z. The Zulu time zone (Z) is equivalent to Coordinated Universal Time (**UTC**) and is often referred to as the military time zone. The military time zone system ensures clear communication in a concise manner, and avoids confusion when coordinating across time zones ([Wikipedia](https://en.wikipedia.org/wiki/Military_time_zone#:~:text=The%20Zulu%20time%20zone%20(Z,when%20coordinating%20across%20time%20zones.)).*

In Boomi, there are two different datetime formats to explicitly express a time zone.

…’Z’ - stands for the character ‘Z’ : ****2023-08-30 10:14:00**Z**
…Z - stands for UTC Offset ****±00:00 : 2023-08-30 10:14:00+00:00

No matter which format you chose, the datetime will be accurate because it contains location (time-zone) information. Personally, I prefer the ‘Z’ character to exchange UTC normalized datetime in any case: yyyy-MM-dd HH:mm:ss**'Z'**

Note: The only difference between yyyy-MM-dd**T**HH:mm:ss and yyyy-MM-dd HH:mm:ss is the format. One has the T character in between date and time, the other hasn’t. The T does not have any impact on the time-zone nor does it contain any information about it.

Implicit time-zone

An implicit time-zone comes into play when you work with datetimes that do not contain a explicit time-zone information. Imagine a SQL Server query:

SELECT Id, Name, CreatedDateTime from Contracts

Name

CreatedDateTime

Contract A

2023-08-23 10:24:00

Contract B

2023-08-23 10:34:00

In such case you need an agreement which time-zone to use.

💡 I recommend always using UTC as the agreed time-zone.

This means, we would interpret 10:24 as UTC time, so that the German user would see 12:24 and the UK user 11:24 - not taking daylight savings into account.