Date and Time Data Types
SQL Server enables you to configure columns to support date information. You should always use the date data types for columns if you want the ability to perform date calculations. For example, if you’re creating a table to store employee information and you would like to be able to calculate how long an employee has worked for your company, you must create a column with a date data type to store the employee’s hire date.
SQL Server supports two date data types: datetime and smalldatetime. The following table describes the differences between these data types.
Data Type Size (in bytes) Supported Values datetime 8 (4 bytes each for date and time) Date: 1/1/1753 AD to 12/31/9999 AD; Time: in milliseconds past midnight smalldatetime 4 (2 bytes each for date and time) Date: 1/1/1900 AD to 6/6/2079; AD Time: number of minutes past midnight
You can enter the date for datetime or smalldatetime columns by using either words (such as January 1, 2002) or numbers (such as 1/1/2002 or 1-1-2002). If you don’t specify the first two digits of the year, SQL Server automatically assumes the first two digits are 20 if the last two digits are 49 or less and 19 if the last two digits are 50 or greater. For example, if you enter a date as 1/1/02, SQL Server automatically converts the date to 1/1/2002.
If you enter a date but not a time, in datetime or smalldatetime columns, SQL Server uses the default time of 12:00 A.M. If you enter a time but not a date, SQL Server uses the default date of January 1, 1900. If you don’t enter either a date or a time, SQL Server uses the default value of January 1, 1900 12:00 A.M.
You can configure SQL Server to display the values in the datetime or smalldatetime columns in several different formats. For example, you can display dates in both alphabetic and numeric formats. You can display times in several different formats, including hours:minutes and hours:minutes:seconds:thousandths of seconds.
Use the following syntax to specify the date and time data types as part of the CREATE TABLE statement.
Lesson4:CreatingandManagingTables
125
Data Type SQL Keyword Datetime datetime Smalldatetime smalldatetime
