DatePart() mvSQL database function in Jitterbit App Builder
Summary
Extracts a part from a date or datetime expression.
Syntax
DATEPART(<datepart>, <datetime>)
Parameters
datepart
| Date Part | Aliases | Vendor Support |
|---|---|---|
year | yy, yyyy | All RDBMS vendors. |
quarter | qq, q | SQL Server and SQLite |
month | mm, m | All RDBMS vendors. |
dayofyear | dy, y | SQL Server and SQLite |
day | dd, d | All RDBMS vendors. |
week | wk, ww | SQL Server and SQLite |
hour | hh | All RDBMS vendors. |
minute | mi, n | All RDBMS vendors. |
second | ss, s | All RDBMS vendors. |
The date part must not be quoted.
The date part is not case-sensitive.
datetime
Column or expression of type datetime or date. Some date parts expect a datetime.
Returns
Returns an int that represents the date part.
Remarks
All database vendors use ISO 8601 (weeks starting on Monday, starting at 1 on the first Monday of the year) for this function.
Earlier App Builder versions handled date boundaries differently. For example, SQL Server calculates weeks using Sunday as the start of the week. SQLite, on the other hand, uses Monday.
Examples
| Expression | Returns |
|---|---|
DATEPART(minute, TIMESTAMP('1997-01-02 12:13:14')) | 13 |
DATEPART(hour, TIMESTAMP('1997-01-02 12:13:14')) | 12 |
DATEPART(day, TIMESTAMP('1997-01-02 12:13:14')) | 2 |
DATEPART(month, TIMESTAMP('1997-01-02 12:13:14')) | 1 |
DATEPART(year, TIMESTAMP('1997-01-02 12:13:14')) | 1997 |