Date/time computed column functions¶

This section provides syntax and examples for the date/time computed column functions you can use with the Data Prep Compute tool.

To use a date/time function, the value must be stored as a datetime data type. Datetime data types can be identified by the datetime data type icon in the header row. If the value is not stored as a datetime data type, convert the value to a datetime data type using the DATEVALUE function. See the DATEVALUE () section of this article.

DATE¶

Takes three separate arguments and combines them to form a date in a new DateTime column.

Syntax

DATE(YEAR, MONTH, DATE)

• YEAR is four-digit value
• MONTH is two-digit value
• DATE is two-digit value

Example

DATE(@year@, @month@, @day@)

Notes on use

Leading zeros for MONTH and DATE are not supported, for example:

DATE(1999,05,08) should be expressed as DATE(1999,5,8)

DATEADD¶

Calculates the date that is so many days, weeks, months from a given date.

Syntax

DATEADD(DATETIME, INCREMENT, INTERVAL)

• DATETIME is the date you want to start with.
• INCREMENT is the number you provide to be added to the to the DATETIME.
• INTERVAL is the interval (minutes, days, years, etc.) to add by. The following is a list of the recognized values for INTERVAL:
  • Years
  • Months
  • Weeks
  • Days
  • Hours
  • Minutes
  • Seconds
  • Millis

Example

DATEADD(@Date Received@, 6, "months")

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object. The INCREMENT provided must be an integer. Millis accepts a maximum of +/- 2147483647.

DATEDIFF¶

Calculates the days, weeks, months between two dates.

Syntax

DATEDIFF(DATETIME_1, DATETIME_2, INTERVAL)

• DATETIME_1 is the date you want to start with.
• DATETIME_2 is the date you want to end with.
• INTERVAL is the interval type (minutes, days, years, etc.) you want returned. The following is a list of the recognized values for the INTERVAL value:
  • Years
  • Months
  • Weeks
  • Days
  • Hours
  • Minutes
  • Seconds
  • Millis

Example

DATEDIFF(@Date Received@, @Date Shipped@, “months”)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object. The INCREMENT provided must be an integer. Millis accepts a maximum of +/- 2147483647.

It is recommended you use the latest datetime value for the DATETIME_2. If you enter the earliest date as the DATETIME_2 value, the DATEDIFF function will return a negative number.

DATEDIFF always rounds the result down to the nearest whole number. For example, if the difference between two dates is 3 years and 11 months, the DATEDIFF function returns the difference as 3 years.

DATEFORMAT¶

Converts a value stored as a datetime data type to a text sting in a given format.

Syntax

DATEFORMAT(DATETIME, FORMAT)

• DATETIME is the date you want to convert.
• FORMAT is format you want the DATETIME converted to.

Example

DATEFORMAT(@Date Received@, “dd-MMM-yyyy HH:mm”)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

DATETRUNC¶

Removes the unwanted detail of a timestamp and rounds it to the interval you want. This provides the same output as the SQL DATE_TRUNC() function. Use case: you want to explore trends in your Community user signups and you need to aggregate signup event data by the time each event occurred. You’re only interested in signups by year, month, or day but not the hour, minute, and millisecond. Use DATETRUNC to remove the portion of the timestamp that you don't need.

Syntax

DATETRUNC(x) where x can be any one of the following arguments.

• minutes
• month
• weeks
• days
• hours
• seconds

Example

DATETRUNC(@DATE@, "months")

DATEVALUE¶

Converts a datetime text string to a datetime object so you can use it for calculations.

Syntax

DATEVALUE(DATETIME, FORMAT, TIME_ZONE)

• DATETIME is the datetime as a text string.
• FORMAT is format of the DATETIME.
• TIME_ZONE is the time zone you want associated with the datetime object.

Example

DATEVALUE(@Date@, "yyyy-MMM-dd hh:mm a", "GMT-05:00")

Notes on use

Use the DATEVALUE function to convert a text column into a date column, or a date that you type into a date object. With the resulting data object, you can use Data Prep date functions, for example return the number of days or years between two dates (see next example below for Date Manipulation).

Date objects can store a date, time, or a combination of date and time.

To convert text to a Data Prep date object: Specify its format in Data Prep date format syntax. Repeated characters indicate the length of the field, such as yyyy means a 4-digit year.

For column DateCol with input text that specifies February 28, 2012 as:

2012/28/02

Convert to date object:

DATEVALUE(@DateCol@, "yyyy/dd/MM")

The date format must match your input data.

If February 28, 2012 looks like:

2012-15-02

Use date format:

"yyyy-dd-MM"

If February 28, 2012 looks like:

2-28-12

Use date format:

"dd-MM-yy"

If the time 1:29 pm looks like:

13:29

Use time format:

"HH:mm"

If the time 1:29 pm looks like:

01:29PM

Use time format:

"hh:mmaa"

Advanced example

If the input text is a date and time, separated by the letter T, then a time zone:

2012-02-28T09:29:00-05:00

For letter characters that literally appear in input text, surround the letter with single straight quotes. Use the following date format:

"yyyy-MM-dd'T'HH:mm:ssZZ"

Date manipulation¶

Use the DATEDIFF function to calculate differences in ts-date-time values between two Data Prep date objects. Calculate days between August 1, 1998 and a date column:

DATEDIFF(DATEVALUE("01-AUG-1998" , "dd-MMM-yyyy"), @MyDate@ , "days")

DAY¶

Extracts the day from a date.

Syntax

DAY(DATETIME)

DATETIME is the date you want to extract the day from.

Example

DAY(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range from 1 to 31.

DAYOFWEEK¶

Returns the day of the week from a date.

Syntax

DAYOFWEEK(DATETIME)

DATETIME is the date you want to evaluate.

Example

DAYOFWEEK(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range from 1 (Monday) to 7 (Sunday).

DAYOFYEAR¶

Returns the day of the year from a date.

Syntax

DAYOFYEAR(DATETIME)

DATETIME is the date you want to evaluate.

Example

DAYOFYEAR(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range 1 to 365 (366 on a leap year).

ENDOFMONTH¶

Returns the datetime for the last day of the month in a new DateTime column. This provides the same output as Excel's EOMONTH function.

Syntax

ENDOFMONTH(DATE_TIME)

DATE_TIME is a DateTime object.

Example

ENDOFMONTH(@Date@)

FROMUNIXTIME¶

Returns a ts-date-time object from a Unix timestamp. This provides the same output as the MySQL FROM_UNIXTIME() function.

Syntax

FROMUNIXTIME(MILLISECONDS)

MILLISECONDS is the int value represented as milliseconds.

Example

FROMUNIXTIME(@UNIX TIME STAMP@)

HOUR¶

Extracts the hour from a time.

Syntax

HOUR(DATETIME)

DATETIME is the time you want to extract the hour from.

Example

HOUR(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range from 0 (12:00 am) to 23 (11:00 pm).

MAXDATE¶

Compares two or more dates and returns the latest date in the comparison.

Syntax

MAXDATE(DATETIME_1, [DATETIME_2, ...])

• DATETIME_1 is the first date.
• DATETIME_2, ... [optional] are the additional dates.

Example

MAXDATE(@Target Ship Date@ ,@Date Shipped@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Here's how the MAXDATE function will respond to some common scenarios:

• If only one date is provided, the provided date is returned.

• The time zone of all the dates are temporarily converted to the same time zone to determine the latest date. The conversion is neither a permanent nor a visual transformation.

• Cells with text strings are ignored. Blank cells are ignored.

• Cells with errors are ignored.

• If no datetime objects are found, a blank cell is returned.

MIDNIGHT¶

Resets the given time to midnight (00:00).

Syntax

MIDNIGHT(DATETIME)

DATETIME is the time you want to reset.

Example

MIDNIGHT(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

The time zone isn't affected.

MINDATE¶

Compares two or more dates and returns the earliest date in the comparison.

Syntax

MINDATE(DATETIME_1, [DATETIME_2, ...])

• DATETIME_1 is the first date.
• DATETIME_2, ... [optional] are the additional dates.

Example

MINDATE(@Target Ship Date@ ,@Date Shipped@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Here's how the MINDATE function will respond to some common scenarios:

• If only one date is provided, the provided date is returned.

• The time zone of all the dates are temporarily converted to the same time zone to determine the latest date. The conversion is neither a permanent nor a visual transformation.

• Cells with text strings are ignored. Blank cells are ignored.

• Cells with errors are ignored.

• If no datetime objects are found, a blank cell is returned.

MINUTE¶

Extracts the minute from a time.

Syntax

MINUTE(DATETIME)

DATETIME is the time you want to extract the minute from.

Example

MINUTE(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range from 0 to 59.

MONTH¶

Extracts the month from a date.

Syntax

MONTH(DATETIME)

DATETIME is the date you want to extract the month from.

Example

MONTH(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range from 1 (January) to 12 (December).

NETWORKDAYS¶

Returns the number of working days between two ts-date-time objects. This provides the same output as Excel's NETWORKDAYS function.

Syntax

NETWORKDAYS(DATE_TIME_START, DATE_TIME_END)

• DATE_TIME_START is a ts-date-time object for start date.
• DATE_TIME is a ts-date-time object for end date.

Example

NETWORKDAYS(@DATE@, DATE(2019,1,12))

NOW¶

Returns the current date and time.

Syntax

NOW(TIME_ZONE) returns the current date and time.

TIME_ZONE, optional, sets the time zone.

Example

NOW("GMT-03:00")

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

If a time zone is not specified in the function, the returned datetime object will default to Greenwich Mean Time (GMT). See the Date and Time Syntax article for a list of time zones and their appropriate syntax.

QUARTER¶

Returns the quarter as an integer from a given ts-date-time object.

Syntax

QUARTER(DDATE_TIME)

DATE_TIME is a ts-date-time object.

Example

QUARTER(@DATE@)

SECOND¶

Extracts the seconds from a time.

Syntax

SECOND(DATETIME)

DATETIME is the time you want to extract the seconds from.

Example

SECOND(@Date@)

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

Returned values range from 0 to 59.

SETTIMEZONE¶

Changes the time zone of a time to the time zone you specify.

Syntax

SETTIMEZONE(DATETIME, TIME_ZONE)

• DATETIME is the time you want to set the time zone of.
• TIME_ZONE is the time zone you want associated with the datetime object.

Example

SETTIMEZONE(@Date Received@, "GMT-3:00")

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.

The transformation doesn't change the time, it simply assigns a new time zone to the existing time. See the Date and Time Syntax article for a list of time zones and their appropriate syntax.

TODAY¶

Returns the current date, doesn't include the time.

Syntax

TODAY()

Example

TODAY()

WEEKOFYEAR¶

Returns the week number as an integer from a given ts-date-time object. This provides the same output as Excel's WEEKNUM function.

Syntax

WEEKOFYEAR(DATE_TIME)

DATE_TIME is a ts-date-time object.

Example

WEEKOFYEAR<@DATE@>

WORKDAY¶

Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. This provides the same output as Excel's WORKDAY function. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Syntax

WORKDAY(STARTDATE, DAYS)

• STARTDATE is a date that represents the start date.
• DAYS is the number of nonweekend and nonholiday days before or after start date. A positive value for days yields a future date; a negative value yields a past date.

Example

WORKDAY(@DATE@ ,12)

YEAR¶

Extracts the year from a date.

Syntax

YEAR(DATETIME)

DATETIME is the date you want to extract the year from.

Example

Year()

Notes on use

The DATETIME you provide must be a datetime object, a column that contains a datetime object, or a function that returns a datetime object.