Using Date and Time Data

Microsoft® SQL Server™ has the datetime and smalldatetime data types to store date and time data.

There are no separate time and date data types for storing only times or only dates. If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00AM (Midnight).

On datetime data you can perform operations such as:

• Entering new or changing existing dates.
• Performing date and time calculations, such as adding or subtracting dates. For more information about date arithmetic, see + (Add) and - (Subtract).
• Searching for a particular date and/or time.

You can perform some arithmetic calculations on datetime data with the system date functions. For more information, see Functions.

Here are some guidelines when using date and time data:

• To search for an exact match on both date and time, use an equal sign (=). Microsoft SQL Server returns date and time values exactly matching the month, day, and year and at the precise time of 12:00:00:000 AM (default).
• To search for a partial date or time value, use the LIKE operator. SQL Server first converts the dates to datetime format and then to varchar. Because the standard display formats do not include seconds or milliseconds, you cannot search for them with LIKE and a matching pattern, unless you use the CONVERT function with the style parameter set to 9 or 109. For more information about searching for partial dates or times, see LIKE.
• SQL Server 7.0 evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting. For example, this view works correctly for connections made with the language set to U.S. English, but not for connections made using other languages:

  CREATE VIEW USA_Dates AS

  SELECT *

  FROM Northwind.dbo.Orders

  WHERE OrderDate < 'May 1, 1997'

    

  When you use datetime constants in queries that are executed by connections using different language settings, care must be taken to ensure 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. For more information on date formats that are interpreted the same by all language settings, see Writing International Transact-SQL Statements.

SQL Server recognizes date and time data enclosed in single quotation marks (‘) in these formats:

• Alphabetic date formats (for example, ‘April 15, 1998’)
• Numeric date formats (for example, ‘4/15/1998’, ‘April 15, 1998’)
• Unseparated string formats (for example, ‘19981207’, ‘December 12, 1998’)