Dates in SQL Server
Before showing you how to calculate dates automatically, let me explain a little about dates in general. SQL Server uses two data types to store both date and time information:
- The datetime data type uses eight bytes of storage. It can accept values ranging from January 1, 1753, through December 31, 9999, and is accurate to within 3.3 milliseconds.
- The smalldatetime data type uses only four bytes of storage. However, it can only accept values from January 1, 1900, through June 6, 2079, and is only accurate to within one minute.
SQL Server does not come with any data type that stores just date or time information. If you enter a date without specifying a time, such as "01/05/2001," the default time selected will be midnight.
Date Functions in SQL Server
SQL Server contains some very useful functions to manipulate dates. GETDATE() returns the current date and time. SQL Server 2000 has also added the GETUTCDATE() function that returns the date and time in GMT.
To compare dates, you can use the DATEDIFF() function. This function takes three arguments: the date "part" to return the difference in and the two dates you want to compare. For example,
select datediff (dd,getdate(),'April 16, 2001')
returns the number of days you have to file your tax return. (In 2001 the due date for filing tax returns was April 16th; normally it's the 15th.) If you wanted the result in hours, you would simply change the first argument from "dd" for dates to "hh" for hours.
The DATEADD() function allows you to add time to an existing date value. For example,
returns the date and time three hours from now. You can also obtain a particular part of a date by using the DATEPART() function. This means that
returns the day of the month. See Listing 1
for some examples of these functions in action.
Now to solving the problem that this 10-Minute Solution raises!