Storing "Time" in database- What to use DateTime/Interger/VarChar

I want to save Date and Time of the user on various activities performed. For date I have decided to use DateTime Column in Database and for Time I am in dilemma what datatype to go for.

I know in sql server 2008 Time datatype has been introduced but I am using older version i.e. Sql Server 2005 so I need your suggest to prove my understanding true or false.

I have seen people using varchar or DateTime for storing time into database. But I am looking towards usage of Integer datatype.

Reason for my selection is performance.

Following is the justification that I am giving to myself.

Assumptions

Any data saved into database must agree following rules

Date will be stored in format mm/dd/yyyy hh:MM:ss where hh:MM:ss will always be 00:00:00 Time will be stored in valid format (from hh:MM:ss as hhMMss)

if hh is 00 
  then MMss
and if MM is 00
  then ss
and if ss is 00 
  then 0

hh will range in between 0-23
MM will range in between 0-59
ss will range in between 0-59

i.e. few examples
00:00:00 = 0
00:01:00 = 100
01:00:00 = 10000
13:00:00 = 130000

Personal thought why it will perform better.

SELECT * FROM Log WHERE loginDate = '05/23/2011' 
AND loginTime BETWEEN 0 AND 235959 --Integer Comparison

When using JOINS on the basis开发者_如何学Go of DateTime considering join for Date part only.

JOIN two tables on the basis of Common Dates irrespective of Time.I think Type Conversion would heavily impact in such cases if using DateTime as the storage datatype.

Since Sql will have to do an integer comparison and no typecasting would be required hence it should perform better.

EDIT

One drawback I just identified is when I want to get the difference between two times that how much time has been spent between 3 days, hopefully then it would become a nightmare to manage throughout the application.

So why do you need 2 columns. If the DateTime column (loginDate) has an empty time 00:00:00 why not just use that empty space for loginTime and have one column.

WHERE loginDate >= '05/23/2011' AND loginDate < '05/24/2011'

If you're intent on using an integer, there's nothing wrong with it.

Bearing your edit in mind, your ideal solution is to put both date and time in the same column, a DATETIME:

• You can then trivially figure the difference between start and end times with DATEDIFF
• You can easily establish just the date portion with CONVERT(varchar(10), loginDate, 101)
• You can easily establish just the time portion with CONVERT(varchar(10), loginDate, 108)

Storage issues might be resolved by using SMALLDATETIME, if precision < 1minute isn't required. SMALLDATETIME requires four bytes per column, which is the same as INTEGER, so you're making a significant net gain over using two columns.