1. MFour Studio Education Center
  2. Resources
  3. Sandbox 201 - Proficient

Date Functions

Updated

ABOUT THIS DOCUMENT: 

The index of date functions includes: ConvetTimeZone, DataAdd, Datediff, DateFormat, DateFromUnix, DatePart, DateParse, DateTime, DateTrunc, Day, EndofMonth, Hour, LastDAy, MakeDate, Minute, Month. Now, Quarter, Second, Today, Weekday, WeekendName and Year.

ConvertTimezone

Converts a UTC date to a different time zone.

Formula

ConvertTimezone(date, timezone)

  • date (required): The UTC date to convert.
  • timezone (required): The name of the IANA timezone to convert the input date into.

Example

ConvertTimezone([Date], "America/Los_Angeles")

  • Converts all of the dates in the [Date] column to Pacific Time.

ConvertTimezone(Date("2014-07-18 10:58:00"), "America/New_York")

  • 2014-07-18 06:58:00

DateAdd

Adds the specified quantity of time to a date.

Formula

DateAdd(unit, amount, date)

  • unit (required) Unit of the amount of time to add. You can choose to add either “year”, "quarter", “month”, “week”, “day”, “hour”, “minute”, or “second”.
  • amount (required) The quantity of time to add.
  • date (required) The date or column of dates you are adding to.

NOTE: The quantity to add is rounded to the nearest whole number.

Example

DateAdd("minute", 60, Date("1999-12-31 23:00:00"))

  • Returns “2000-01-01 00:00:00” adding 60 minutes to the date.

DateAdd(“day”, 7, [Date])

  • Adds 7 days to every date in a column of dates.

 

DateDiff

Calculates the time difference between two dates.

Formula

DateDiff(unit, start, end)

  • unit (required) Unit to use for the difference. You can choose either “year”, "quarter", “month”, “week”, “day”, “hour”, “minute”, or “second”.
  • start (required) The starting date.
  • end (required) The ending date.If end is before start the result is negative.

NOTE: The result is rounded to a whole number. If end is before start the result is negative.

Example

DateDiff(“day”, [Invoice Date], Today())

  • The time in days between the current UTC date and the Invoice Date.

DateDiff("year", [Invoice Date], Date("2018-01-01")) = 8

  • The time, in years, from [Invoice Date] and January 1, 2018.

DateDiff("hour", [Ticket Date], Now()) = 103

  • The time, in hours, from the Ticket Date to the current UTC date and time.

DateFormat

Formats a date value into text based on the given a format.

Formula

DateFormat(date, format)

  • date [required] The date or column of dates
  • format [required] The format to apply. See all supported format

Examples

Example #1

  • DateFormat(Date("2018-07-23"), "%B")
    • Returns July.

Example #2

  • DateFormat([Date], "%a %B %d %Y")
    • Returns a formatted text version of each row in the [Date] column.
    • The format applied — "%a %B %d %Y" — translates to abbreviated weekday name (%a), month name (%B), day number (%d), and year (%Y).

mceclip3.png

DateFromUnix

Convert a Unix-style timestamp (seconds since January 1, 1970) to a Date.

Formula

DateFromUnix(number)

  • number (required) A column of numbers that indicates the Unix-style time stamp.

NOTE: When given a number, the Date function behaves the same as DateFromUnix.

Example

DateFromUnix(0)

  • Returns 1970-01-01 00:00:00

DateFromUnix(1503724894)

  • Returns 2017-08-26 05:21:34

DatePart

Extract the specified date part, e.g. “month”, from the given date.

Formula

DatePart(precision, date, [timezone])

  • precision (required) Date part to be extracted, chosen from “year”, "quarter", “month”, “week”, “day”, "weekday", “hour”, “minute”, “second”, “millisecond”, and "epoch".
  • date (required) Date from which date part is to be extracted.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g.”America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC. Note: The output returned will be presented in the organization timezone. This may cause days, months, or years to appear offset if the specified timezone is ahead of the organization timezone. If you want to see the output presented in the specified timezone, you can apply ConvertTimezone to it.

Example

DatePart("year", [Invoice Date])

  • Extracts the year from the invoice dates.

DatePart("week", Date("2007-01-10 10:00:00"))

  • Returns 2, the week number of this date.
  • Weeks in Studio are considered to start on Sunday.

 

DateParse

DateParse parses a Text value into a Date using a provided format.

 

Formula

DateParse(text, format)

  • text (required) - The text value to be parsed.
  • format (required) - The format to apply to the text value when parsing. This is the format represented in the text value. See format options.

For example, the format of "03/Sep/2020:03:05:13" is "%d/%b/%Y:%H:%M:%S", and the format of "03/Sep/2020" is "%d/%b/%Y".  DateParse will convert both of these values to "2020-09-03 03:05:13".

Examples

DateParse("03-Sep-2020:03:05:13", "%d-%b-%Y:%H:%M:%S")

mceclip4.png

DateParse([Text Date], "%d/%b/%Y:%H:%M:%S")

mceclip5.png

DateParse([Text Date], "%d/%b/%Y")

mceclip6.png

DateParse([Text Date], "[%d/%b/%Y]")

mceclip7.png

DateTime

Convert the value into a date and time.

Formula

DateTime(input)

  • input (required) The input you would like to be interpreted as a date. Input can be text, number, or date. The given Text value can be a date or a date and time. When inputting a text value for a date, the best format is yyyy-mm-dd hh:mm:ss. If given a Number Date behaves like DateFromUnix.

 

DateTrunc

Truncate the date to the specified date part, e.g.”month”.

Formula

DateTrunc(precision, date, [timezone])

  • precision (required) Smallest date part to preserve, chosen from “year”, "quarter", “month”, “week”, “day”, “hour”, “minute”, and “second”.
  • date (required) Date to be truncated.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g.”America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC. Note: The output returned will be presented in the organization timezone. This may cause days, months, or years to appear offset if the specified timezone is ahead of the organization timezone. If you want to see the output presented in the specified timezone, you can apply ConvertTimezone to it.

Example

DateTrunc("hour", Date("1980-05-22 8:45:30"))

  • Returns 1980-05-22 8:00:00 discarding the “minute” and”second” components of the date.

DateTrunc("day", Date("1980-05-22 8:45:30"))

  • Returns 1980-05-22 00:00:00 discarding the time components of the date.

Day

Returns the day of the month of the given date as a number.

Formula

Day(date, [timezone])

  • date (required) The date from which to extract the day component.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g. “America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC.

Example

Day(Date("2007-08-14"))

  • Returns 14

EndOfMonth

Returns the last day of the month for a given date.

Formula

EndOfMonth(date)

  • date (required) The date from which the month is to be computed.

Example

Extract the last day of the month from the [Invoice Date] column:

EndOfMonth([Invoice Date])

mceclip8.png

Hour

Returns the hour component of the given date as a number.

Formula

Hour(date, [timezone])

  • date (required) The date from which to extract the hour component.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g. “America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC.

Example

Hour(Date("2007-08-14 07:11:00"))

  • Returns 7.

LastDay

Returns the last day of the specified date part (eg “month”) for a given date.

Formula

LastDay(date, precision)

  • date (required) The date from which the last day of the date part is to be computed.
  • precision (required) The date part to be extracted, chosen from “year”, “quarter” “month”, or “week”.

Examples

(1) Extract the last month from the [Invoice Date] column:

LastDay([Invoice Date], "month")

mceclip9.png

(2) Extract the last year from an explicit date:

LastDay(Date("2020-01-10 10:34:29"), “year”)

mceclip10.png

MakeDate

Makes a date from the given year, month, and day of the month.

Formula

MakeDate(year, month, day)

  • year (required): The year.
  • month (required): The month.
  • day (required): The day.

Example

MakeDate(2019,1,31)

  • 2019-01-31 00:00:00

Minute

Returns the minute component of the given date as a number.

Formula

Minute(date, timezone)

  • date (required) The date from which to extract the minute component.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g. “America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC.

Example

Minute(Date("2007-08-14 07:11:00"))

  • Returns 11.

Month

Returns the month component of the given date as a number.

Formula

Month(date, [timezone])

  • date (required) The date from which to extract the month component.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g. “America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC.

Example

Month(Date("2007-08-14"))

  • Returns 8.

Now

Returns the current UTC date and time.

Formula

Now()

Example

DateDiff(“minute”, [Invoice Date], Now())

  • The time in hours between the current UTC date and time, and the Invoice Date.

Quarter

Returns the quarter component of the given date, as a number.

Formula

Quarter(date)

  • date (required) The date to display the quarter of

NOTE: First day of the first Quarter is set at January 1.

Example

Quarter([DATE])

  • Returns which quarter each date occurs in.

Quarter(Date("2007-08-14 07:11:05"))

  • Returns 3.

Second

Returns the second component of the given date as a number.

Formula

Second(date)

  • date (required) The date from which to extract the second component.

Example

Second(Date("2007-08-14 07:11:05"))

  • Returns 5.

Today

Returns the current UTC date.

Formula

Today()

Example

DateDiff(“day”, [Invoice Date], Today())

  • The time in hours between the current UTC date and the Invoice Date.

 

Weekday

Returns the day of the week as Sunday (1) to Saturday (7).

Formula

Weekday(date, [timezone])

  • date (required) The date from which to return the weekday component.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g. “America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC.

NOTE: Sunday is 1 and Saturday is 7.

Example

Weekday(Date("2017-12-01"))

  • Returns 6

 

WeekendName

Returns the name of the weekday that matches the input date.

Formula

WeekdayName(date)

  • date (required) The date from which to extract the weekday name.

Examples

Example #1

WeekdayName(Date("2007-08-14"))

  • Returns Tuesday.

Example #2

WeekdayName([Date])

  • Return the weekday name for every row in the [Date] column.

mceclip11.png

Year

Returns the year component of the given date as a number.

Formula

Year(date ,[timezone])

  • date (required) The date from which to extract the year component.
  • timezone (optional) Name of IANA timezone to get the date part at, e.g. “America/Los_Angeles”. When calculating a timezone, input dates are treated as UTC.

Example

Year(Date("2007-08-14"))

  • Returns 2007

 

 

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.

Articles in this section