Chapter 15: Using the Built-In Functions in Queries

Date functions

The date built-in functions perform arithmetic operations and display information about datetime, smalldatetime, date, and time values.

Adaptive Server stores values with the datetime datatype internally as two four-byte integers. The first four bytes store the number of days before or after the base date, January 1, 1900. The base date is the system’s reference date. datetime values earlier than January 1, 1753 are not permitted. The other four bytes of the internal datetime representation store the time of day to an accuracy of 1/300 second.

The date datatype is stored as four bytes. The base date is January 1, 0001 through December 31, 9999. The time datatype covers time from 12:00:00AM through 11:59:59:999PM.

The smalldatetime datatype stores dates and times of day with less precision than datetime. smalldatetime values are stored as two two-byte integers. The first two bytes store the number of days after January 1, 1900. The other two bytes store the number of minutes since midnight. Dates range from January 1, 1900 to June 6, 2079, with accuracy to the minute.

The default display format for dates looks like this:

Apr 15 1997 10:23PM

See “Using the general purpose conversion function: convert” for information on changing the display format for datetime or smalldatetime. When you enter datetime, smalldatetime, date, and time values, enclose them in single or double quotes. Adaptive Server may round or truncate millisecond values.

Adaptive Server recognizes a wide variety of datetime data entry formats. For more information about datetime, smalldatetime, date, and time values, see Chapter 8, “Creating Databases and Tables,” and Chapter 7, “Adding, Changing, and Deleting Data.”

Table 15-10 lists the date functions and the results they produce:

**Table 15-10: Date functions**
Function	Argument	Result
current_date	Date	Returns the current date
current_time	Date	Returns the current time
day	(date_expression)	Returns an integer that represents the day in the datepart of a specified date
datename	(datepart, date)	Part of a datetime, smalldatetime, date or time value as an ASCII string
datepart	(datepart, date)	Part of a datetime, smalldatetime, date or time value (for example, the month) as an integer
datediff	(datepart, date, date)	The amount of time between the second and first of two dates, converted to the specified date part (for example, months, days, hours)
dateadd	(datepart, number, date)	A date produced by adding date parts to another date
getdate	()	Returns current system date and time.
getutcdate	()	Returns a datetime whose value is in Coordinated Universal Tiime (sometimes called Greenwich Mean Time). This value is not adjusted for time/zone, and it is not cached; a new value is returned every time the function is called.
month	(date_expression)	Returns an integer that represents the month in the datepart of a specified date
year	(date_expression)	Returns an integer that represents the year in the datepart of a specified date

The datename, datepart, datediff, and dateadd functions take as arguments a date part—the year, month, hour, and so on. The datename function produces ASCII values where appropriate, such as for the day of the week.

datepart returns a number that follows ISO standard 8601, which defines the first day of the week and the first week of the year. Depending on whether the datepart function includes a value for calweekofyear, calyearofweek, or caldayofweek, the date returned may be different for the same unit of time. For example, if Adaptive Server is configured to use U.S. English as the default language:

datepart(cyr, "1/1/1989")

returns 1988, but:

datepart(yy, "1/1/1989)

returns 1989.

This disparity occurs because the ISO standard defines the first week of the year as the first week that includes a Thursday and begins with Monday.

For servers using U.S. English as their default language, the first day of the week is Sunday, and the first week of the year is the week that contains January 4th.

Table 15-11 lists each date part, its abbreviation (if there is one), and the possible integer values for that date part.

**Table 15-11: Date parts**
Date part	Abbreviation	Values
year	yy	1753–9999
quarter	qq	1–4
month	mm	1–12
week	wk	1–54
day	dd	1–31
dayofyear	dy	1–366
weekday	dw	1– 7 (Sunday–Saturday)
hour	hh	0–23
minute	mi	0–59
second	ss	0–59
millisecond	ms	0–999

select datename (mm, "1997/06/16")
-----------
        June
(1 row affected)

select datediff (yy, "1984", "1997")
----------
     13

(1 row affected)

select dateadd (dd, 16, "1997/06/16")
------------------------------
           Jul  2 1997 12:00AM


(1 row affected)

The values of weekday are affected by the language setting.

Some examples of the week date part:

select datepart(cwk,"1997/01/31")

-----------
          5
 
(1 row affected)

select datepart(cyr,"1997/01/15")

-----------
       1997
 
(1 row affected)

select datepart(cdw,"1997/01/24")

-----------
          5
 
(1 row affected)

Table 15-12 lists the week number date parts, their abbreviations, and values.

**Table 15-12: Week number date parts**
Date part	Abbreviation	Values
calweekofyear	cwk	1–52
calyearofweek	cyr	1753–9999
caldayofweek	cdw	1–7 (1 is Monday in us_english)

View this book as PDF