Type Conversion Functions

Question 1

DATE

Answer 1

Returns a date given a number, string, or date expression.

Examples:

DATE([Employee Start Date])
DATE(“April 15, 2004”) = #April 15, 2004#
DATE(“4/15/2004”)
DATE(#2006-06-15 14:52#) = #2006-06-15#

Quotation marks are required in the second and third examples.

Question 2

DATETIME

Answer 2

Returns a datetime given a number, string, or date expression.

Example:

DATETIME(“April 15, 2005 07:59:00”) = April 15, 2005 07:59:00

Question 3

DATEPARSE

Answer 3

Converts a string to a datetime in the specified format. Support for some locale-specific formats is determined by the computer’s system settings. Letters that appear in the data and do not need to be parsed should be surrounded by single quotes (‘ ‘). For formats that do not have delimiters between values (for example, MMddyy), verify that they are parsed as expected. The format must be a constant string, not a field value. This function returns Null if the data does not match the format.

This function is available for several connectors. For more information, see Convert a Field to a Date Field.

Examples:

DATEPARSE (“dd.MMMM.yyyy”, “15.April.2004”) = #April 15, 2004#
DATEPARSE (“h’h’ m’m’ s’s’”, “10h 5m 3s”) = #10:05:03#

Question 4

FLOAT

Answer 4

Casts its argument as a floating point number.

Examples:

FLOAT(3) = 3.000
FLOAT([Age]) converts every value in the Age field to a floating point number.

Question 5

INT

Answer 5

Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero.

Examples:

INT(8.0/3.0) = 2
INT(4.0/1.5) = 2
INT(0.50/1.0) = 0
INT(-9.7) = -9

When a string is converted to an integer it is first converted to a float and then rounded.

Question 6

STR

Answer 6

Casts its argument as a string.

Example:

STR([Age])

This expression takes all of the values in the measure called Age and converts them to strings.