Skip to Content

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