The type of date/time information to return. The name (or abbreviation) of a date or time part. This name can be specified in uppercase or lowercase, with or without enclosing quotes. The datepart can be specified as a literal or a host variable.
The DATEPART function returns the datepart information about a specified date/time expression as an integer. To return this information as a character string, use DATENAME. If the datepart value is sqltimestamp (or sts), the DATEPART can return either data type TIMESTAMP or INTEGER, as described below.
DATEPART is provided for Sybase and Microsoft SQL Server compatibility.
The datepart argument can be one of the following date/time components, either the full name (the Date Part column) or its abbreviation (the Abbreviation column). These datepart component names and abbreviations are not case-sensitive.
0-999 (with precision of 3).
SQL_TIMESTAMP: yyyy-mm-dd hh:mm:ss
The preceding table shows the default return values for the various date parts. You can modify the returned values for several of these date parts by using the SET OPTION command with various time and date options.
week: Caché can be configured to determine the week of the year for a given date using either the Caché default algorithm or the ISO 8601 standard algorithm. For further details, refer to the WEEK function.
weekday: The Caché default for weekday is to designate Sunday as first day of the week (weekday=1). However, you can configure the first day of the week to another value, or you can apply the ISO 8601 standard which designates Monday as first day of the week. For further details, refer to the DAYOFWEEK function. Note that the ObjectScript $ZDATE and $ZDATETIME functions count week days from 0 through 6 (not 1 through 7).
second: If the date-expression contains fractional seconds, Caché returns second as a decimal number with whole seconds as the integer component, and fractional seconds as the decimal component. Precision is not truncated.
millisecond: Caché returns three fractional digits of precision, with trailing zeroes removed. If the date-expression has more than three fractional digits of precision, Caché truncates it to three digits.
sqltimestamp: Caché converts the input data to timestamp format and supplies zero values for the time elements, if necessary. Can return either data type TIMESTAMP or data type INTEGER (see below). The sqltimestamp (abbreviated sts) datepart value is for use only with DATEPART. Do not attempt to use this value in other contexts.
A datepart can be specified as a quoted string, without quotes, or with parentheses around a quoted string. No literal substitution is performed on datepart, regardless of how specified; literal substitution is performed on date-expression. All datepart values return a data type INTEGER value, except sqltimestamp (or sts), which returns its value as a character string of data type TIMESTAMP.
where delimiter is a slash (/), hyphen (-), or period (.).
If the year is given as two digits, Caché checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the Legacy Documentation chapter in Using InterSystems Documentation. For information on setting the sliding window for the current process, see the documentation for the ObjectScript $ZDATE, $ZDATEH, $ZDATETIME and $ZDATETIMEH functions.
DATEPART performs the following checks on date-expression values. If a value fails a check, the null string is returned.
A valid date-expression may consist of a date string (yyyy-mm-dd), a time string (hh:mm:ss), or a date and time string (yyyy-mm-dd hh:mm:ss). If both date and time are specified, both must be valid. For example, you can return a Year value if no time string is specified, but you cannot return a Year value if an invalid time string is specified.
A date string must be complete and properly formatted with the appropriate number of elements and digits for each element, and the appropriate separator character. For example, you cannot return a Year value if the Day value is omitted. Years must be specified as four digits.
A time string must be properly formatted with the appropriate separator character. Because a time value can be zero, you can omit one or more time elements (either retaining or omitting the separator characters) and these elements will be returned with a value of zero. Thus, 'hh:mm:ss', 'hh:mm:', 'hh:mm', 'hh::ss', 'hh::', 'hh', and ':::' are all valid. To omit the Hour element, date-expression must not have a date portion of the string, and you must retain at least one separator character (:).
Date and time values must be within a valid range. Years: 1841 through 9999. Months: 1 through 12. Days: 1 through 31. Hours: 0 through 23. Minutes: 0 through 59. Seconds: 0 through 59.
The number of days in a month must match the month and year. For example, the date '0229' is only valid if the specified year is a leap year.
Most date and time values less than 10 may include or omit a leading zero. However, an Hour value of less than 10 must include the leading zero if it is part of a datetime string. Other non-canonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.
If date-expression specifies a time format but does not specify a date format, DATEPART does not perform range validation for the time component values.
In the following example, each DATEPART returns the year portion of the datetime string (in this case, 2004) 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: