Date functions

The date functions perform arithmetic operations and display information about datetime, bigtime, bigdatetime, smalldatetime, date, and time values. You can use them in the select list or the where clause of your query.

Use datetime datatypes for values later than January 1, 1753; use date for dates from January 1, 0001 to January 1, 9999. Enclose the date values in double or single quotes. Adaptive Server recognizes many different date formats. See the Reference Manual: Building Blocks for more information about datatypes.

This is the default display format:

Apr 15 2010 10:23PM

Each date is divided into parts with abbreviations recognized by Adaptive Server. Table 16-2 lists each date part, its abbreviation (if there is one), and possible integer values for that part.

**Table 16-2: Date parts**
Date part	Abbreviation	Values
year	yy	1753 – 9999 (datetime) 1900 – 2079 (smalldatetime) 0001 – 9999 (date)
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
microsecond	us	0 – 999999

For example, use the datediff function to calculate the amount of time in date parts between the first and second of the two dates you specify. The result is a signed integer value equal to date2 - date1 in date parts.

This query finds the number of days between pubdate and November 30, 2010:

select pubdate, newdate = datediff(day, pubdate,
    "Nov 30 2010") 
from titles

pubdate 		                 newdate
------------------------ ---------------
Jun 12 2006 12:00AM                1632               
Jun  9 2005 12:00AM                2000
Jun 30 2005 12:00AM                1979
Jun 22 2004 12:00AM                2352
Jun  9 2006 12:00AM                1635
Jun 15 2004 12:00AM                2356
...

Use the dateadd function to add an interval (specified as a integer) to a date you specify. For example, if the publication dates of all the books in the titles table slipped three days, you could get the new publication dates with this statement:

select dateadd(day, 3, pubdate) 
from titles


---------------------
Jun 15 2006 12:00AM             
Jun 12 2005 12:00AM             
Jul  3 2005 12:00AM             
Jun 25 2004 12:00AM             
Jun 12 2006 12:00AM             
Jun 21 2004 12:00AM             
...