Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

For more information on supported date formatting strings, see DATEFORMAT Function.

Two-digit year values

Depending on the system, a two-digit value for year in a Datetime value is subject to different interpretations. In 

D s product
, two-digit values for the year that are older than 80 years from the current year are forward-ported into the future. For example, in a job run on Dec 31, 2021, the date 01/01/41 is interpreted as 01/01/1941. However, if the job is run the next day (January 01, 2022), then the same data is interpreted as 01/01/2041.

Other systems use different limits for backward versus forward porting of year values:

As a result, it can be a challenge to manage these system-dependent two-digit years in a consistent manner. 

Tip

Tip: For best results, you should format year values as four-digit values before the data is ingested into

D s product
. Four-digit years are consistently represented across all systems.

If the above is not possible, you can create replacement steps in your recipe to convert two-digit years to four-digit values. In the following example, 00-39 is interpreted as a 19XX year, while 40-99 is interpreted as a 20XX year: 

D trans
RawWrangletrue
p03Value19$1
Typestep
WrangleText##NO_WRANGLE##
p01NameColumn
p01ValuemyDateColumn
p02NameFind
p02Value/\b([456789][0-9])\b$/
p03NameReplace with
SearchTermReplace text or pattern

and

D trans
RawWrangletrue
p03Value20$1
Typestep
WrangleText##NO_WRANGLE##
p01NameColumn
p01ValuemyDateColumn
p02NameFind
p02Value/\b([0123][0-9])\b$/
p03NameReplace with
SearchTermReplace text or pattern

Job Execution

Datetime data typing involves the basic type definition, plus any supported formatting options. Depending on where the job is executed, there may be variation in how the Datetime data type is interpreted. 

...