Caché SQL Reference
DATENAME
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A date/time function that returns a string representing the value of the specified part of a date/time expression.
Synopsis
DATENAME(datepart,date-expression)
Arguments
datepart 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.
date-expression A date/time expression from which the date part is to be returned. date-expression must contain a value of type datepart.
Description
The DATENAME function returns the name of the specified part (such as the month "June") of a date/time value. The result is returned as data type VARCHAR(20). If the result is numeric (such as "23" for the day), it is still returned as a VARCHAR(20) string. To return this information as an integer, use DATEPART. To return a string containing multiple date parts, use TO_DATE.
Note that DATENAME is provided for Sybase and Microsoft SQL Server compatibility.
This function can also be invoked from Caché ObjectScript using the DATENAME() method call:
$SYSTEM.SQL.DATENAME(datepart,date-expression)
Datepart Argument
The datepart argument can be a string containing one (and only 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.
Date Part Abbreviations Return Values
year yyyy, yy 1840-9999
quarter qq, q 1-4
month mm January,...December
week wk, ww 1-53
weekday dw Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
dayofyear dy, y 1-366
day dd, d 1-31
hour hh 0-23
minute mi, n 0-59
second ss, s 0-59 (with fractional seconds, if provided)
millisecond ms 0-999 (with precision of 3)
If you specify an invalid datepart value as a literal, an SQLCODE -8 error code is issued. However, if you supply an invalid datepart value as a host variable, no SQLCODE error is issued and the DATENAME function returns a value of NULL.
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.
second: Caché returns a string containing the date-expression value for seconds and (if provided) fractional seconds with the precision of date-expression.
millisecond: Caché returns a string containing the number of milliseconds (thousandths of a second). If the date-expression has more than three fractional digits of precision, Caché truncates it to three digits and returns this number as a string.
A datepart can be specified as a quoted string or without quotes. These syntax variants perform slightly different operations:
Date Expression Formats
The date-expression argument can be in any of the following formats:
The Caché %String (or compatible) value can be in any of the following formats:
Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
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 Caché ObjectScript $ZDATE, $ZDATEH, $ZDATETIME and $ZDATETIMEH functions.
Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
Range validation is not performed for time components. Fractional seconds are truncated.
Range and Value Checking
DATENAME performs the following checks on input values. If a value fails a check, the null string is returned.
Examples
In the following example, each DATENAME returns 'Wednesday' because that is the day of week ('dw') of the specified date:
SELECT DATENAME('dw','2004-02-25') AS DayName,
       DATENAME(dw,'02/25/2004') AS DayName,
       DATENAME('DW',59590) AS DayName
 
The following example returns 'December' because that is the month name ('mm') of the specified date:
SELECT DATENAME('mm','1999-12-20 12:00:00') AS MonthName
 
The following example returns '1999' (as a string) because that is the year ('yy') of the specified date:
SELECT DATENAME('yy','1999-12-20 12:00:00') AS Year
 
Note that the above examples use the abbreviations of the date parts. However, you can specify the full name, as in this example:
SELECT DATENAME('year','1999-12-20 12:00:00') AS Year
 
The following example returns the current quarter, week-of-year, and day-of-year. Each value is returned as a string:
SELECT DATENAME('Q',$HOROLOG) AS Q,
       DATENAME('WK',$HOROLOG) AS WkCnt,
       DATENAME('DY',$HOROLOG) AS DayCnt
 
The following Embedded SQL example passes in the datepart and date-expression as a host variables:
  SET a="year"
  SET b=$HOROLOG
  &sql(SELECT DATENAME(:a,:b) INTO :c)
  WRITE "this year is: ",c
 
The following example uses a subquery to returns records from Sample.Person whose day of birth was a Wednesday:
SELECT Name AS WednesdaysChild,DOB
FROM (SELECT Name,DOB,DATENAME('dw',DOB) AS Wkday FROM Sample.Person)
WHERE Wkday='Wednesday'
ORDER BY DOB
 
See Also