A general date/time function
that returns a CHARACTER STRING containing the name of the value of a specified
part of a date/time expression.
Synopsis
DATENAME(datepart,date-expression)
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 a CHARACTER STRING. If the result is numeric (such as "23" for the day),
it is still returned as a CHARACTER STRING. To return this information as
an integer, use
DATEPART. To return a string
containing multiple date parts, use
TO_DATE.
The
datepart parameter 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 component names and abbreviations are not case-sensitive.
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.
Note that
DATENAME is provided for Sybase and Microsoft
SQL Server compatibility.
-
A Caché %Date logical value (+$H)
-
A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
-
A Caché %String (or compatible) value
The Caché %String (or compatible) value can be in any of the
following 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
(.).
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
If the year is given as two digits, the %DATE utility's sliding-date
window is used (if applicable) to determine the century.
DATENAME performs the following checks on input
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 though 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 '02–29' 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.
The following examples all return 'Wednesday' because that is the day
of week ('dw') of the specified date:
SELECT DISTINCT DATENAME('dw','2004-02-25') AS DayName
FROM Sample.Person
SELECT DISTINCT DATENAME('dw','02/25/2004') AS DayName
FROM Sample.Person
SELECT DISTINCT DATENAME('dw',59590) AS DayName
FROM Sample.Person
The following example returns 'December' because that is the month name
('mm') of the specified date:
SELECT DISTINCT DATENAME('mm','1999-12-20 12:00:00') AS MonthName
FROM Sample.Person
The following example returns '1999' (as a string) because that is the
year ('yy') of the specified date:
SELECT DISTINCT DATENAME('yy','1999-12-20 12:00:00') AS Year
FROM Sample.Person
Note that the above examples use the abbreviations of the date parts.
However, you can specify the full name, as in this example:
SELECT DISTINCT DATENAME('year','1999-12-20 12:00:00') AS Year
FROM Sample.Person
The following example returns the full SQL timestamp:
SELECT DISTINCT DATENAME('sts','1999-12-20') AS TStamp
FROM Sample.Person
In this case,
DATENAME fills in the missing time
information to return a timestamp string of '1999–12–20 00:00:00'.