Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Advantage of using date and time in 2008 over 2005 datetime Expand / Collapse
Posted Thursday, October 3, 2013 2:13 AM


Group: General Forum Members
Last Login: Friday, January 10, 2014 4:43 AM
Points: 15, Visits: 44
Hi Everyone,

Hope all is well.

We can store date and time separately in 2008,but when we are using 2005 we have only datetime and smalldatetime data types.

I need to know what is the advantage of using these separated data types?Whether its about memory storage or something else?

Please text me the answer.

Thank you,
Post #1501047
Posted Thursday, October 3, 2013 2:21 AM



Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 15,501, Visits: 13,163
You only use date and time if you need them seperately.
For example, for a lot of data you only need the date part. The birthdate is a good example.

In SQL Server 2008 you can use the date data type, in 2005 you need to use datetime and store an unnecessary time portion. (in most cases 00:00:00)

Another example is the opening hours of a store. In 2008, you can use the time data type, in 2005 still datetime. This resulted in a "dummy" date, such as 1900-01-01, together with the actual time portion.

With the seperate data types, you can be more efficient and store only what is necessary.

Furthermore, if you need to store an actual point in time (date and time portion), you can use datetime2 in 2008, which has far better precision.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1501048
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse