Skip to content

Latest commit

 

History

History
75 lines (66 loc) · 9.26 KB

data-flow-date-time-functions.md

File metadata and controls

75 lines (66 loc) · 9.26 KB
Raw
title titleSuffix description author ms.author ms.service ms.subservice ms.custom ms.topic ms.date
Date and time functions in the mapping data flow
Azure Data Factory & Azure Synapse
Learn about date and time functions in mapping data flow.
kromerm
makromer
data-factory
data-flows
synapse
conceptual
01/05/2024

Date and time functions in mapping data flow

[!INCLUDEappliesto-adf-asa-md]

[!INCLUDEdata-flow-preamble]

The following articles provide details about date and time functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows.

Expression functions list

In Data Factory and Synapse pipelines, use date and time functions to express datetime values and manipulate them.

Expression function Task
add Adds a pair of strings or numbers. Adds a date to a number of days. Adds a duration to a timestamp. Appends one array of similar type to another. Same as the + operator.
addDays Add days to a date or timestamp. Same as the + operator for date.
addMonths Add months to a date or timestamp. You can optionally pass a timezone.
between Checks if the first value is in between two other values inclusively. Numeric, string and datetime values can be compared
currentDate Gets the current date when this job starts to run. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. The local timezone is used as the default. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
currentTimestamp Gets the current timestamp when the job starts to run with local time zone.
currentUTC Gets the current timestamp as UTC. If you want your current time to be interpreted in a different timezone than your cluster time zone, you can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', or 'America/Cayman'. It's defaulted to the current timezone. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html. To convert the UTC time to a different timezone, use fromUTC().
dayOfMonth Gets the day of the month given a date.
dayOfWeek Gets the day of the week given a date. 1 - Sunday, 2 - Monday ..., 7 - Saturday.
dayOfYear Gets the day of the year given a date.
days Duration in milliseconds for number of days.
fromUTC Converts to the timestamp from UTC. You can optionally pass the timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. It's defaulted to the current timezone. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
hour Gets the hour value of a timestamp. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. The local timezone is used as the default. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
hours Duration in milliseconds for number of hours.
isDate Checks if the input date string is a date using an optional input date format. Refer to Java's SimpleDateFormat for available formats. If the input date format is omitted, default format is yyyy-[M]M-[d]d. Accepted formats are [ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ]
isTimestamp Checks if the input date string is a timestamp using an optional input timestamp format. Refer to Java's SimpleDateFormat for available formats. If the timestamp is omitted the default pattern yyyy-[M]M-[d]d hh:mm:ss[.f...] is used. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. Timestamp supports up to millisecond accuracy with value of 999 Refer to Java's SimpleDateFormat for available formats.
lastDayOfMonth Gets the last date of the month given a date.
millisecond Gets the millisecond value of a date. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. The local timezone is used as the default. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
milliseconds Duration in milliseconds for number of milliseconds.
minus Subtracts numbers. Subtract number of days from a date. Subtract duration from a timestamp. Subtract two timestamps to get difference in milliseconds. Same as the - operator.
minute Gets the minute value of a timestamp. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. The local timezone is used as the default. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
minutes Duration in milliseconds for number of minutes.
month Gets the month value of a date or timestamp.
monthsBetween Gets the number of months between two dates. You can round off the calculation. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. The local timezone is used as the default. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
second Gets the second value of a date. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. The local timezone is used as the default. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
seconds Duration in milliseconds for number of seconds.
subDays Subtract days from a date or timestamp. Same as the - operator for date.
subMonths Subtract months from a date or timestamp.
toDate Converts input date string to date using an optional input date format. Refer to Java's SimpleDateFormat class for available formats. If the input date format is omitted, default format is yyyy-[M]M-[d]d. Accepted formats are :[ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ].
toTimestamp Converts a string to a timestamp given an optional timestamp format. If the timestamp is omitted the default pattern yyyy-[M]M-[d]d hh:mm:ss[.f...] is used. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. Timestamp supports up to millisecond accuracy with value of 999. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
toUTC Converts the timestamp to UTC. You can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. It is defaulted to the current timezone. Refer to Java's SimpleDateFormat class for available formats. https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
weekOfYear Gets the week of the year given a date.
weeks Duration in milliseconds for number of weeks.
year Gets the year value of a date.

Related content