date and time data types -sql server 2008 March 10, 2008Posted by fofo in SQL Server 2008.
Tags: Sql Server, SQL Server 2008
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()
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()
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()
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’
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