|
|
Transact-SQL User's Guide |
The date built-in functions display information about dates and times. They manipulate datetime and smalldatetime values, performing arithmetic operations on them.
Adaptive Server stores values with the datetime datatype internally as two 4-byte integers. The first 4 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 4 bytes of the internal datetime representation store the time of day to an accuracy of 1/300 second.
The smalldatetime datatype stores dates and times of day with less precision than datetime. smalldatetime values are stored as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 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 or smalldatetime 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 and smalldatetime values, see Chapter 7, "Creating Databases and Tables," and Chapter 8, "Adding, Changing, and Deleting Data."
The following table lists the date functions and the results they produce:
Function | Argument | Result |
|---|---|---|
getdate | () | Current system date and time. |
datename | (datepart, date) | Part of a datetime or smalldatetime value as an ASCII string. |
datepart | (datepart, date) | Part of a datetime or smalldatetime 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. |
The datename, datepart, datediff, and dateadd functions take as arguments a date partžthe year, month, hour, and so on. Table 10-11 lists each date part, its abbreviation (if there is one), and the possible integer values for that date part. The datename function produces ASCII values where appropriate, such as for the day of the week.
Date Part | Abbreviation | Values |
|---|---|---|
year | yy | 1753-9999 |
quarter | 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 |
For example:
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)
Note that the values of the weekday date part are affected by the language setting.
The datepart function accepts the following date parts for the week number:
Some examples of the week number date parts:
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 10-12 lists the week number date parts, their abbreviations and values
Date Part | Abbreviation | Values |
|---|---|---|
calweekofyear | cwk | 1-52 |
calyearofweek | cyr | 1753-9999 |
caldayofweek | cdw | 1-7 (1 is Monday in us_english) |
The getdate function produces the current date and time in Adaptive Server internal format for datetime values. getdate takes the NULL argument, ().
To find the current system date and time, type:
select getdate()
--------------------------
Aug 19 1997 12:45PM
(1 row affected)
You might use getdate in designing a report so that the current date and time are printed every time the report is produced. getdate is also useful for functions such as logging the time a transaction occurred on an account.
To display the date using milliseconds, use the convert function, for example:
select convert(char(26), getdate(), 109)
--------------------------
Aug 19 1997 12:45:59:650PM
(1 row affected)
See "Changing the Display Format for Dates" for more information.
The datepart and datename functions produce the specified part of a datetime or smalldatetime valuežthe year, quarter, day, hour, and so onžas either an integer or an ASCII string. Since smalldatetime is accurate only to the minute, when a smalldatetime value is used with either of these functions, seconds and milliseconds are always 0.
The following examples assume an April 12 date:
select datepart(month, getdate())
--------------
8
(1 row affected)
select datename(month, getdate())
-------------
August
(1 row affected)
The datediff function calculates the amount of time in date parts between the first and second of the two dates you specifyžin other words, it finds the interval between the two dates. The result is a signed integer value equal to date2 - date1 in date parts.
This query uses the date November 30, 1990 and finds the number of days that elapsed between pubdate and that date:
select pubdate, newdate = datediff(day, pubdate,
"Nov 30 1990")
from titles
For the rows in the titles table having a pubdate of October 21, 1990, the result produced by the previous query is 40, the number of days between October 21 and November 30. To calculate an interval in months, the query is:
select pubdate, interval = datediff(month, pubdate,
"Nov 30 1990")
from titles
This query produces a value of 1 for the rows with a pubdate in October 1990 and a value of 5 for the rows with a pubdate in June 1990. When the first date in the datediff function is later than the second date, the resulting value is negative. Since two of the rows in titles have values for pubdate that are assigned using the getdate function as a default, these values are set to the date that your pubs database was created and return negative values (-65) in the two preceding queries.
If one or both of the date arguments is a smalldatetime value, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are automatically set to 0 for the purpose of calculating the difference.
The dateadd function adds 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 1986 12:00AM
Jun 12 1988 12:00AM
Jul 3 1985 12:00AM
Jun 25 1987 12:00AM
Jun 12 1989 12:00AM
Jun 21 1985 12:00AM
Jul 6 1997 1:43PM
Jul 3 1986 12:00AM
Jun 15 1987 12:00AM
Jul 6 1997 1:43PM
Oct 24 1990 12:00AM
Jun 18 1989 12:00AM
Oct 8 1990 12:00AM
Jun 15 1988 12:00AM
Jun 15 1988 12:00AM
Oct 24 1990 12:00AM
Jun 15 1985 12:00AM
Jun 15 1987 12:00AM
(18 rows affected)
If the date argument is given as a smalldatetime, the result is also a smalldatetime. You can use dateadd to add seconds or milliseconds to a smalldatetime, but it is meaningful only if the result date returned by dateadd changes by at least one minute.
|
|