When to use separate date and time instead of a single datetime

Question

If I want to store date and time, is it better to store them in a separate date and time or use a single datetime?

When should we use date and time instead of a single datetime?

I want to filter my queries either using date or time.

Answer 1

When you are talking about a moment in time, whether a universal moment, or a specific date and time on someone's local calendar, you use a datetime. If you want to be sure that you are talking about an exact moment in time, regardless of the observer, then you use a datetimeoffset.

If you are storing just a date then you mean a date without a time component, meaning "any time on this date".

If you are storing just a time then you mean a time without a date component, meaning "this time on any date", or "this time on a date determined by some other means".

There is no practical purpouse to having both a date and a time that are about the same thing, sitting on the same row. Just use a datetime for that.

Answer 2

In SQL Server 2008 you have date and time data types so this becomes a non issue.

If it is good choice it really depends by your business and how you will query you data.

If for example you want to know all the orders places between 1 and 2 PM for any day using a separated Date and Time column will make it quicker