New DATETIME Data Types in 2008

We all know that in previous versions of SQL Server 2008 we have just one data type for storing date and time data, DATETIME.  The problem is sometimes we only needed date, or we only needed time. Now you have some new options!

TIME

DECLARE@myTime TIME
SET @myTime = GETDATE()
SELECT@myTime as myTime

By default, SQL Server 2008 will store 7 decimal places of precision. if you need less, you can indicate that! Let’s say I only want 3 decimal places (to more closely emulate the precision from older versions of SQL Server).

DECLARE@myTime TIME(3)
SET@myTime = GETDATE()
SELECT @myTime as myTime

Pretty nice, right?

DATE

If you need to store just a DATE, you have that ability now! A DATE value can be anything from 0001-01-01 through 9999-12-31.

DECLARE @myDate DATE
SET @myDate = GETDATE()
SELECT @MyDate as myDate

DATETIME2

Now that we have the new DATE data type, and the new TIME data type, if you want to combine them, you could use DATETIME, but if you wanted the extra precision of the TIME data type, then you’ll want to use DATETIME2.

DECLARE @myDate DATETIME2
SET @myDate = GETDATE()
SELECT @MyDate as myDate

As for now, DATETIME is still sticking around. I haven’t seen anything that says it’s deprecated, or non-standard to use it, but now that you have DATETIME2, maybe you can use the extra precision provided.

If you have any questions, send them in, I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

0 comments

  1. Good work! Thank you!
    I always wanted to write in my blog something like that. Can I take part of your post to my site?
    Of course, I will add backlink?

    Regards, Reader

Leave a comment

Your email address will not be published. Required fields are marked *