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 9, “Adding, Changing, and Deleting Data.”

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

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

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)

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 11-12 lists the week number date parts, their abbreviations, and values:

---

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF