Date formats can often be challenging for beginners, particularly when different data sources supply dates in different formats, and your reporting requirements specify yet another final date format.
There are two main functions for dealing with date formats.
Date() and Date#()
Often I see people using the two functions in different combinations with a bit of trial and error until they get the result they want, without really understanding why it has worked. But, with a few simple basics understood, this is easy to understand.
First: Dates are a dual data type
Date fields, once properly formatted, are a dual data type. This means that each value in the field has two components, a string (text) representation of the date, and an underlying numeric component.
You may have seen when working with dates in Microsoft Excel that sometimes when you change the format of a date cell you end up with 42019 instead of 15/01/2015. This number is a difference, in days, between the date and an “epoch date”, in this case 01/01/1900. This is done, because this way it’s much easier to subtract one date from another to find the difference or add a number of days, months or years to a date to forecast for example.
Second: Each function manipulates only one component of that dual data type
Date#() takes a date string (e.g. the text ‘15/01/2015’) and calculates and adds the numeric component to make the proper dual data type date field. It leaves the string component unchanged.
Date() takes a number (either the number 42019 or an already properly interpreted date field with the underlying numeric component already in place) and calculates and adds (or changes) the string component. It leaves the numeric component unchanged.
Both functions take an optional second parameter as a format string for how to interpret in the case of Date() or how to format in the case of Date#(). If this second parameter is not provided, the default set in the script variables is assumed.
Example: A string in the format 2015-01-15 needs to be loaded, interpreted as a date, and formatted to 15/01/2015.
Let's assume the source field is called [OrderDate].
Solution: Date(Date#([OrderDate], 'YYYY-MM-DD'), 'DD/MM/YYYY') as Date
Reading from the inside out, we take the [OrderDate] field, apply Date#() to interpret the string and calculate the appropriate numerical component using the format mask YYYY-MM-DD, and then apply Date() to that result to reformat the string representation using the format mask DD/MM/YYYY.
Aside: Timestamps
Times and Timestamps take this simple concept one step further. In this case a time is again represented by a string (e.g. '12:00:00' or '15/01/2015 12:00:00') and the underlying numerical component will represent the time as a decimal fraction of a day (e.g. 0.5 or 42019.5). The corresponding functions are Time() and Time#() or Timestamp() and Timestamp#().
