For sqltimestamp, time is returned as a 24-hour clock. Fractional
seconds are truncated.
Invalid Argument Error Codes
If you specify an invalid datepart option, DATEPART generates an SQLCODE -8 error code, and the following
%msg: 'badopt' is not a recognized DATEPART option.
If you specify an invalid date-expression value (for example, an alphabetic text string), DATEPART generates an SQLCODE -400 error code, and the following %msg: Invalid input to DATEPART() function: DATEPART('year','badval'). If you specify a date-expression that fails
validation (as described below), DATEPART generates
an SQLCODE -400 error code, and the following %msg: Unexpected
error occurred: <ILLEGAL VALUE>datepart.
Examples
In the following example, each DATEPART returns the year portion of the datetime string (in this case, 2018)
as an integer. Note that date-expression can be
in various formats, and datepart can be specified
as either the datepart name or datepart abbreviation, quoted or unquoted:
SELECT DATEPART('yy','2018-02-22 12:00:00') AS YearDTS,
DATEPART('year','2018-02-22') AS YearDS,
DATEPART(YYYY,'02/22/2018') AS YearD,
DATEPART(YEAR,64701) AS YearHD,
DATEPART('Year','64701,23456') AS YearHDT
The following example returns the current year and quarter,
based on the $HOROLOG value:
SELECT DATEPART('yyyy',$HOROLOG) AS Year,DATEPART('q',$HOROLOG) AS Quarter
The following example returns the birth day-of-week for the
Sample.Person table, ordered by day of week:
SELECT Name,DOB,DATEPART('weekday',DOB) AS bday
FROM Sample.Person
ORDER BY bday,DOB
In the following example, each DATEPART returns 20 as the minutes portion of the date-expression string:
SELECT DATEPART('mi','2018-2-20 12:20:07') AS Minutes,
DATEPART('n','2018-02-20 10:20:') AS Minutes,
DATEPART(MINUTE,'2018-02-20 10:20') AS Minutes
In the following example, each DATEPART returns 0 as the seconds portion of the date-expression string:
SELECT DATEPART('ss','2018-02-20 03:20:') AS Seconds,
DATEPART('S','2018-02-20 03:20') AS Seconds,
DATEPART('Second','2018-02-20') AS Seconds
The following example returns the full SQL timestamp as a TIMESTAMP
data type. DATEPART fills in the missing time
information to return a timestamp of '2018-02-25 00:00:00':
SELECT DATEPART(sqltimestamp,'2/25/2018') AS DTStamp
The following example supplies a date and time in $HOROLOG format,
and returns a timestamp of '2018-02-22 06:30:56':
SELECT DATEPART(sqltimestamp,'64701,23456') AS DTStamp
The following example uses a subquery with DATEPART to return those people whose birthday is leap year day (February
29th):
SELECT Name,DOB
FROM (SELECT Name,DOB,DATEPART('dd',DOB) AS DayNum,DATEPART('mm',DOB) AS Month FROM Sample.Person)
WHERE Month=2 AND DayNum=29
