SQL Server: Data Type DATETIME or Only DATE

SQL Server 2008 introduced a new data type “DATE” to store date data only. Before this we have only DATETIME data type which stores both date and time. I have observed that many database developers and DBAs still prefer DATETIME to store data, even for columns where they don’t need to store time. For example, to store date of birth we need only date, and not the time.

SELECT Emp_Name, Emp_SSN, Emp_DOB

FROM Employee

WHERE CONVERT(VARCHAR(20), Emp_DOB, 101) <= '06/30/2005'

Problem occurs when we need to retrieve such data, and we need different conversion functions to separate date from time. And if such conversion functions are part of WHERE clause, then it prevents the optimizer from choosing the index on the column. Resultantly, a poor query performance, because indexes are not being used.

Simply select DATE data type for such columns, especially when you don’t need the time portion. Even, if most of queries use conversion functions to separate date or time. Save such data separately in two different columns with data type DATE and TIME respectively.