jump to navigation

date and time data types -sql server 2008 March 10, 2008

Posted by fofo in SQL Server 2008.
Tags: ,

in this post i am going to introduce the date and time data types that ship with sql server 2008. up to this point sql server had a datetime and smalldatetime data types to support date and time data. developers experienced many problems with those data types. sometimes we did not have enough precision or some other times we did have too much information.for example many times we had to convert the datetime to character string,strip the time component and then convert back to datetime.lets see some examples of the new data type

DECLARE @mybeautifulDate date

SELECT @mybeautifulDate = GETDATE()

PRINT @mybeautifulDate

execute the above statement

execute the following sql statement

DECLARE @Date dateSELECT @Date = ‘1977-02-17’


with the new date data type we only need 3 bytes of data to store our data since we do not store the time component.

lets have a look at the new time data type. in a new sql query window type and execute the following

DECLARE @MyTime time = GETDATE(),

@MyTime7 time(7) = GETDATE(),

@MyTime6 time(6) = GETDATE(),

@MyTime5  time(5) = GETDATE(),

@MyTime4 time(4) = GETDATE(),

@MyTime3 time(3) = GETDATE(),

@MyTime2 time(2) = GETDATE(),

@MyTime1 time(1) = GETDATE(),

@MyTime0 time(0) = GETDATE()


PRINT @MyTime7

PRINT @MyTime6

PRINT @MyTime5

PRINT @MyTime4

PRINT @MyTime3

PRINT @MyTime2

PRINT @MyTime1

PRINT @MyTime0

 the results shoule be like these






the number inside the parenthesis indicate the precision we want to have for our data.it takes 3-5 bytes in the server to store time data depending on the precision we desire.

there is another date data type, namely datetime2. it provides us with more precision that we had with the existing datetime data types.

if we type this sql statement

DECLARE @datetime2 as DATETIME2(7) = GETDATE()

PRINT @datetime2

the result will be

2008-03-10 22:05:49.4200000we have a more precise datetime datatype.another new date data type is the datetimeoffset that has the same precision as datetime2 but stores the GMT offset as well.

DECLARE @datetimeoffset as DATETIMEOFFSET(7) = ‘1977-02-17 14:30:00 -09:00’

PRINT @datetimeoffset

if i execute this sql statement the result will be

1977-02-17 14:30:00.0000000 -09:00

it requires 10 bytes of storage.

hope it helps



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: