CODEWITHSUNDEEP
sqlsql-serverdatetime
Harke
Harke

Reputation: 1289

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

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.

Upvotes: 17

Views: 11393

Answers (4)

crosan
crosan

Reputation: 486

If you intentionally do not care about the time, it's more efficient to store this data as a date datatype. Think a customer birthday column, there's not too many cases I can think of that would use this time. If there happens to be a time attached to it (often a bug), this needs to be removed via a convert statement in order to do a compare. It also consumes additional space if you don't need these values (3 bytes compared to 8).

I think it's similar to having a status code table with the id as a bigint instead of a tinyint or the like (depending on how many status codes you would plan to have).

It's just a matter of what you're using the data for, if you think there's a good chance you'll ever need the that data, then use datetime, otherwise use date.

Upvotes: 2

Pandian
Pandian

Reputation: 9126

Nothing brilliant about separating date and time,

Better you save date and time in Same column,

Here they have discussed the same issue check it : are-there-any-good-reasons-for-keeping-date-and-time-in-separate-columns

you can also get date and time separately by query

 SELECT 
    CONVERT(VARCHAR(10),GETDATE(),111) as DatePart,
    convert(varchar(15), getdate(), 108) TimePart

Upvotes: 1

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241485

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.

Upvotes: 12

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26727

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

Upvotes: 11

Related Questions