Sql server® 2012 t-sql fundamentals

Working with date and Time Separately

SQL Server 2008 introduced separate DATE and TIME data types, but in previous versions there is no 

separation between the two components. If you want to work only with dates or only with times in 

versions of SQL Server prior to SQL Server 2008, you can use either DATETIME or SMALLDATETIME, 

which contain both components. You can also use types such as integers or character strings on which 

you implement the date and time logic, but I won’t discuss this option here. If you want to use the 

DATETIME or SMALLDATETIME type, when you want to work only with dates, you store the date with 

a value of midnight (all zeros in the time parts). When you want to work only with times, you store the 

time with the base date January 1, 1900. 

For example, the orderdate column in the Sales.Orders table is of a DATETIME data type, but 

because only the date component is actually relevant, all values were stored at midnight. When you 

need to filter only orders from a certain date, you don’t have to use a range filter. Instead, you can use 

the equality operator like this.

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE orderdate = '20070212';

When the character string literal is converted to DATETIME, SQL Server assumes midnight as the 

time component if time is not specified. Because all values in the orderdate column were stored with 

midnight in the time component, all orders placed on the requested date will be returned. Note that 

you can use a CHECK constraint to ensure that only midnight is used as the time part.

If the time component is stored with non-midnight values, you can use a range filter like this.

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE orderdate >= '20070212' 

  AND orderdate < '20070213';

If you want to work only with times in versions prior to SQL Server 2008, you can store all values 

with the base date of January 1, 1900. When SQL Server converts a character string literal that con-

tains only a time component to DATETIME or SMALLDATETIME, SQL Server assumes that the date is 

the base date. For example, run the following code.

SELECT CAST('12:30:15.123' AS DATETIME);

www.it-ebooks.info

---