Home > SQL > Why doesn’t SQL Server allow me to separate DATE and TIME?

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

 

Advertisements
Categories: SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: