DATETIME/DATETIME2 versus DATE and TIME Datatypes

Question

DATETIME/DATETIME2 versus DATE and TIME Datatypes

sixthzenz

Right there with Babe

Points: 740
More actions
January 18, 2011 at 9:29 am

#245550

Can anyone explain why it would be more beneficial to store date and time values separately, rather than just using datetime or datetime2 to store the date-related data? I've typically used datetime for most date related attributes in the past. We've upgraded our infrastructure to SQL Server 2008/2008R2 and, hence, the reason for my question.
Here are my assumptions thus far, but which are accurate?
- using date and time fields separately may reduce the amount of space needed to store the actual datetime
- query speed may be increased if date and time data is separated out (need to confirm)
- building queries will be slightly more complex

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

sixthzenz (1/18/2011)
Can anyone explain why it would be more beneficial to store date and time values separately, rather than just using datetime or datetime2 to store the date-related data?

It's not. It's detrimental. Think of all the logic to put them back together when you need to compare to anything.

Use DATE or TIME when you only need one of them. eg:

Train timetable. The 6am train comes at 6am every day. It's a time that does not have a date associated.

Holiday table. 25th December is Christmas day. All day.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 2

I would only break them out in cases where I need to use them seperately, for example, when a date will NEVER have a time or when we are only concerned about time. Anytime we would consider them as a unit, ie: this date and time, I would leave them together. Also keep in mind anytime you need to put them together requires additional effort..

All in all, unless you have a really good reason, use datetime, unless you need the time zone features or the additional accuracy. Or if you need ONLY a date or ONLY a time.

CEWII

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I see Gail answered while I was formulating my answer.. I agree..

CEWII

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I absolutely agree with Gail and Elliott. Having Date and Time in separate columns is usually going to cause more problems than it fixes. To repeat what Gail said... it's "detrimental".

Also, I wouldn't use DATETIME2 unless it's absolutely necessary for precision.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions