Export (0) Print
Expand All

Using Date and Time Data

Updated: 14 April 2006

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:

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.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft