DateName() mvSQL database function in Jitterbit App Builder
Summary
Returns a part of a date or date/time value with an English string representation for days and months.
Syntax
DATENAME(<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 |
weekday | dw, w | 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.
SQL Server calculates weeks using Sunday as the start of the week. SQLite, on the other hand, uses Monday.
datetime
Column or expression of type datetime or date. Some date parts expect a datetime.
Returns
Returns a string that represents the name of the given date part.
Regardless of whether it is returning a name "Monday", or a value that could be represented numerically (2015), that value will be a string.
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 |
|---|---|
DATENAME(minute, TIMESTAMP('1997-01-02 12:13:14')) | 13 |
DATENAME(hour, TIMESTAMP('1997-01-02 12:13:14')) | 12 |
DATENAME(day, TIMESTAMP('1997-01-02 12:13:14')) | 2 |
DATENAME(month, TIMESTAMP('1997-01-02 12:13:14')) | January |
DATENAME(month, TIMESTAMP('1997-12-02 12:13:14')) | December |
DATENAME(weekday, TIMESTAMP('1997-01-02 12:13:14')) | Thursday |
DATENAME(year, TIMESTAMP('1997-01-02 12:13:14')) | 1997 |