Why doesn't SQL Server allow me to separate DATE and TIME?

Admittedly, this is one of the rare features that Access boasts over SQL Server. The ANSI-92 standard states that compliant database should support the following DATE/TIME datatypes: 

• DATE + TIME
• DATE
• TIME

Unfortunately, SQL Server only supports the first type of column, with the DATETIME (sub-millisecond accuracy) and SMALLDATETIME (minute accuracy) datatypes. If you only insert partial information (such as '10/31/2001' or '3:25 PM'), SQL Server will fill in the rest for you. Try the following script, to see what I mean: 
 

SET NOCOUNT ON  CREATE TABLE #foo  (      dt DATETIME  )  GO    INSERT #foo(dt) VALUES('20011031')  INSERT #foo(dt) VALUES('3:25 PM')    SELECT dt FROM #foo    DROP TABLE #foo  GO

 
Here are the results: 
 

dt   -----------------------  2001-10-31 00:00:00.000  1900-01-01 15:25:00.000

 
Notice that SQL Server inserts midnight when time information is missing, and 1900-01-01 when date information is missing. 
 
So what do you do when you're only interested in one or the other? There are several camps on this one. One is to store the date and/or time information as a CHAR or VARCHAR column. This makes comparisons and sorting very difficult. Another camp suggests storing the extraneous information and ignoring it. Often "ignoring" means "converting", so to get just the date or time from the above table, you would do this: 
 

SELECT dateonly = CONVERT(CHAR(8),dt,112) FROM #foo  SELECT timeonly = CONVERT(CHAR(8),dt,8) FROM #foo

 
Results: 
 

dateonly   --------   20011031  19000101    timeonly   --------   00:00:00  15:25:00

 
Unfortunately, this type of conversion will not take advantage of any index on the DATETIME column. A similar approach is to store a standard value for the part you're not interested in, and handle that part of the data at the application level. 
 
Another way to store only time, and do so efficiently, is to use an integer column. You multiply the number of hours by 100, and add the minutes. For example: 
 

SET NOCOUNT ON  CREATE TABLE #foo  (      tm SMALLINT  )  INSERT #foo VALUES  (      -- e.g. 1527 = 3:27 PM / 15:27      100 * DATEPART(HOUR, GETDATE())      + DATEPART(MINUTE,GETDATE())  )  SELECT tm FROM #foo  DROP TABLE #foo

 
You multiply the hours by 100, then add the minutes, thus getting the time in military format. You could leave the formatting up to the application, or retrieve a nicely formatted time by running this query against #foo (I think this would be prettier in an application that's not so strongly typed): 
 

SELECT      timeonly = CAST(          LEFT(tm, LEN(tm)-2) + ':'          + RIGHT(tm, 2) AS VARCHAR(5)      )      FROM #foo

 
Results: 
 

timeonly  --------  1:17

 
Similarly, to store only date as an integer, you multiply the year by 10000, add the month (multiplied by 100), and then add the day: 
 

SET NOCOUNT ON  CREATE TABLE #foo  (      dt INT  )  INSERT #foo VALUES  (      DATEPART(YEAR, GETDATE()) * 10000          + DATEPART(MONTH, GETDATE()) * 100          + DATEPART(DAY, GETDATE())  )  SELECT dt FROM #foo  DROP TABLE #foo

 
Getting this one into date format is about as pretty as the previous example: 
 

SELECT      CONVERT      (          CHAR(8),          CONVERT          (              DATETIME,              CONVERT              (                  CHAR(8),                  dt              ),              112          )      ) FROM #foo

 
Results: 
 

dateonly  ----------   20011031

 
These latter solutions should only be used for enhancing storage space required for presentation values. If you need to do computations and conversions on these columns, throw the last few solutions out the window.