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. SAP ASE 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 SAP ASE. This table lists each date part, its abbreviation (if there is one), and possible integer values for that part.

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             
...