Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
APPLIES TO: 
Azure Data Factory
Azure Synapse Analytics
Tip
Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!
Data flows are available in both Azure Data Factory pipelines and Azure Synapse Analytics pipelines. This article applies to mapping data flows. If you're new to transformations, refer to the introductory article Transform data using mapping data flows.
This article provides details about date and time functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows.
Expression functions list
In Azure Data Factory and Azure Synapse Analytics 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 time stamp. Appends one array of similar type to another. Same as the + operator. |
| addDays | Adds days to a date or time stamp. Same as the + operator for date. |
| addMonths | Add months to a date or time stamp. You can optionally pass a time zone. |
| between | Checks if the first value is in between two other values inclusively. You can compare numeric, string, and datetime values. |
| currentDate | Gets the current date when this job starts to run. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The local time zone is used as the default. Refer to Kusto's format_datetime() function for available formats. |
| currentTimestamp | Gets the current time stamp when the job starts to run with the local time zone. |
| currentUTC | Gets the current time stamp as UTC. If you want your current time to be interpreted in a different time zone than your cluster time zone, you can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. It defaults to the current time zone. Refer to Kusto's format_datetime() function for available formats. To convert the UTC time to a different time zone, use fromUTC(). |
| dayOfMonth | Gets the day of the month when given a date. |
| dayOfWeek | Gets the day of the week when given a date. For example, 1 is Sunday, 2 is Monday, and continues to 7, which is Saturday. |
| dayOfYear | Gets the day of the year when given a date. |
| days | Gives the duration in milliseconds for the number of days. |
| fromUTC | Converts to the time stamp from UTC. You can optionally pass the time zone in the form of GMT, PST, UTC, or America/Cayman. It defaults to the current time zone. Refer to Kusto's format_datetime() function for available formats. |
| hour | Gets the hour value of a time stamp. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The local time zone is used as the default. Refer to Kusto's format_datetime() function for available formats. |
| hours | Gives the duration in milliseconds for the number of hours. |
| isDate | Checks if the input date string is a date by using an optional input date format. Refer to Kusto's format_datetime() function for available formats. If the input date format is omitted, the 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 time stamp by using an optional input time stamp format. Refer to Kusto's format_datetime() function for available formats. If the time stamp is omitted, the default pattern yyyy-[M]M-[d]d hh:mm:ss[.f...] is used. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The Timestamp function supports up to millisecond accuracy with a value of 999. Refer to Kusto's format_datetime() function for available formats. |
| lastDayOfMonth | Gets the last date of the month when given a date. |
| millisecond | Gets the millisecond value of a date. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The local time zone is used as the default. Refer to Kusto's format_datetime() function for available formats. |
| milliseconds | Gives the duration in milliseconds for the number of milliseconds. |
| minus | Subtracts numbers. Subtracts the number of days from a date. Subtracts the duration from a time stamp. Subtracts two time stamps to get the difference in milliseconds. Same as the - operator. |
| minute | Gets the minute value of a time stamp. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The local time zone is used as the default. Refer to Kusto's format_datetime() function for available formats. |
| minutes | Gives the duration in milliseconds for the number of minutes. |
| month | Gets the month value of a date or time stamp. |
| monthsBetween | Gets the number of months between two dates. You can round off the calculation. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The local time zone is used as the default. Refer to Kusto's format_datetime() function for available formats. |
| second | Gets the second value of a date. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. The local time zone is used as the default. Refer to Kusto's format_datetime() function for available formats. |
| seconds | Gives the duration in milliseconds for the number of seconds. |
| subDays | Subtracts days from a date or time stamp. Same as the - operator for date. |
| subMonths | Subtracts months from a date or time stamp. |
| toDate | Converts an input date string to date by using an optional input date format. Refer to Kusto's format_datetime() function for available formats. If the input date format is omitted, the 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 time stamp when given an optional time stamp format. If the time stamp is omitted, the default pattern yyyy-[M]M-[d]d hh:mm:ss[.f...] is used. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. Timestamp supports up to millisecond accuracy with a value of 999. Refer to Kusto's format_datetime() function for available formats. |
| toUTC | Converts the time stamp to UTC. You can pass an optional time zone in the form of GMT, PST, UTC, or America/Cayman. It defaults to the current time zone. Refer to Kusto's format_datetime() function for available formats. |
| weekOfYear | Gets the week of the year when given a date. |
| weeks | Gives the duration in milliseconds for the number of weeks. |
| year | Gets the year value of a date. |