Using Date and Time Data

The Date and Time data type category in SQL Server 2005 consists of the following data types:

• datetime
• smalldatetime

The datetime and smalldatetime data types allow you to do the following:

• Enter new dates and times or change existing dates and times.
• Add and subtract dates. For more information about date arithmetic, see + (Add) (Transact-SQL) and - (Subtract) (Transact-SQL).
• Search for a particular date or time.

You can perform some arithmetic calculations on the datetime and smalldatetime data types with system date and time functions. For more information, see Functions (Transact-SQL).

Here are some guidelines for using Date and Time data:

• To search for an exact match on both date and time, use an equal sign (=). SQL Server returns date and time values that match exactly the month, day, and year, and at the precise time of 12:00:00:000 A.M. (default). For more information about time, see Time Formats.

• SQL Server evaluates datetime constants at run time. A date string that works for the date formats that are expected by one language might be unrecognizable if the query is executed by a connection that is using a different language and date format setting. For example, this view works correctly for connections that are made with the language set to U.S. English, but not for connections that are made using other language settings:

  CREATE VIEW dbo.USA_Dates AS
  SELECT PurchaseOrderID, TotalDue
  FROM AdventureWorks.Purchasing.PurchaseOrderHeader
  WHERE OrderDate < 'May 1, 2002';
  

  When you use datetime constants in queries that are executed by connections using different language settings, ensure that the dates are acceptable for all the language settings. The same care must be taken with datetime constants in permanent objects in international databases, such as table constraints and view WHERE clauses. The following example shows one method of ensuring a datetime constant is interpreted the same for all language or date format connection settings.

  CREATE VIEW dbo.International_Dates AS
  SELECT PurchaseOrderID, TotalDue
  FROM AdventureWorks.Purchasing.PurchaseOrderHeader
  WHERE OrderDate < CONVERT(DATETIME,'20020501',101);
  

  For more information, see Writing International Transact-SQL Statements.

SQL Server recognizes date and time data that is enclosed in single quotation marks (') in the following formats:

• Alphabetic date such as 'April 15, 1998'
• Numeric date formats, such as '4/15/1998'
• Unseparated string formats. For example, '19981207' would be December 7, 1998.

See Also

Other Resources

Date and Time (Transact-SQL)
Data Types (Transact-SQL)
CAST and CONVERT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance