Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server 2008 Date and Time Data Types

MSSQLTips author Tim Ford By:   |   Read Comments (7)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Dates

Problem
I have a column in one of my Microsoft SQL Server 2005 databases that store date and time data relating to date of birth.  I'm only interested in the date of birth, not time of day, yet the data in this field stores both date and time.  There are only two options for storing this data: either datetime or smalldatetime.  No matter which one I use I end up with time information when I query the database table.  I've heard that SQL Server 2008 offers more options for storing date and time values.  Will any of these fit my needs?

Solution
Until Microsoft SQL Server 2008, we were limited in our storage and handling of date and time data.  Our options consisted of either the datetime or the smalldatetime data type as you expressed.  Both options were a combined field storing both date and time data.  As a result we had to deal with cumbersome CONVERT() functions or involved T/SQL manipulations to massage and cajole data values stored in our databases into formats that were relevant and proper for our needs.  The release of Microsoft SQL Server 2008 brought with it 4 new date and time data types: date, datetime2, datetimeoffset, and time.  Each one of these will provide additional functionality and should significantly reduce the amount of coding overhead associated with managing date and time data.  In this tip I'll be using the following table to demonstrate the differences between date and time data types in SQL 2008:

CREATE TABLE dbo.TempusFugit 
   

   
col_datetime datetime NULL,
   
col_smalldatetime smalldatetime NULL, 
   
col_date date NULL, 
   
col_datetime2 datetime2(7) NULL, 
   
col_datetimeoffset datetimeoffset(7) NULL, 
   
col_time time(7) NULL
   )  
ON 
[PRIMARY] 
GO
 

Our test data will be extremely simple, I'll only be inserting the current system date and time into each column in the table will provide us with varying results via the use of the GETDATE() system function. 

INSERT INTO dbo.TempusFugit (col_datetimecol_smalldatetimecol_date
col_datetime2col_datetimeoffsetcol_time
VALUES (GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE
())
GO

After running this INSERT statement I'll go through the columns individually to examine their associated default formatting.  Additionally, I'll discuss storage requirements, and review (if applicable) what is necessary to provide the same results when using earlier versions of Microsoft SQL Server.  The GETDATE() function returns the value for the current system date and time as a datetime data type.  Therefore for this tip I'll be using the col_datetime column as the representative of the GETDATE() function's results in our comparisons. 

 

SQL 2005 Data Types

The first two columns in this table correspond to existing data types that you'll recognize from SQL Server 2005 and earlier versions.  These data types are still supported in SQL Server 2008: datetime and smalldatetime.  Executing this SELECT query will provide us with a glimpse of those two columns.

SELECT col_datetimecol_smalldatetime
FROM 
dbo.tempusfugit

GO

The datetime and smalldatetime data types are identical with the exception of their behavior in respect to fractional seconds.  The datetime data type presents fractional seconds to the precision of three, whereas smalldatetime will round up or down to the nearest second.  Both data types are presented in 24-hour format.  The inclusion of fractional seconds comes at a price for the datetime data type however.  That price is an additional 4 bytes of storage.  The smalldatetime data type is presented as YYYY-MM-DD HH:MM:SS  and requires 4 bytes of storage.  The datetime data type, the most-commonly used of the date-oriented types, is formatted as YYYY-MM-DD HH:MM:SS[.fractional seconds] and requires those additional 4 bytes of storage I mentioned for a total of 8 bytes.

 

New Data Types Supported in Microsoft SQL Server 2008

Microsoft embraced ISO compliancy in regards to storing date and time data with the release of SQL Server 2008, specifically two of the four new date and/or time data types finally segregate that information.  Let's examine these data types closer.

 

Date Data Type (New in SQL Server 2008)

SELECT col_datetimecol_date
FROM 
tempusfugit
GO

 

The date data type presents itself in simple YYYY-MM-DD format and should better fit the needs of most development scenarios than the current process of translating  a datetime column's value via the CONVERT() function.  Doing so also reduces database storage requirements significantly when compared to a standard datetime column.  Storage requirements are only 3 bytes for the date data type.  At this point I want to touch upon the question that prompted this tip: does SQL 2008 afford you any better storage options for date-only data value storage and retrieval.  The answer is definitely "yes" and definitely "Date data type".  In previous versions of SQL Server, you had three options for storing your date-only information:

  • Store the values in a datetime-typed column and take a 5 byte storage penalty
  • Store the values in a smalldatetime-typed column, assuming a 1 byte storage hit
  • Store the value as a varchar(n) with the data stored in a format that meets the perceived expected output format (YYYY-MM-DD, MM/DD/YYYY, etc.)

Isn't it interesting that the most commonly-used date/time data type, datetime, is not the best option for efficiently storing that specific data?  Unless you need to store fractional seconds, datetime is never the option for storing date/time-related data.  Using a  varchar(n) column to store your data in the format you plan on presenting to your end-users may seem like a good idea, but it would result in storage requirements of 10 bytes when you factor in separation characters and a four character year as well as two characters each for date and month.  Additionally, I pose the question: "What happens if your usage expectations change or vary from user to user?"  No, what was typical is that you had to resort to using the CONVERT() function in T/SQL to convert a stored datetime or smalldatetime value into a format you wished to present to the end-user as is shown in the following examples.

T-SQL Command Executed Result
SELECT CONVERT(VARCHAR(10),col_datetime, 101) FROM dbo.TempusFugit 10/26/2008
SELECT CONVERT(VARCHAR(23),col_datetime, 126) FROM dbo.TempusFugit 2008-10-26T21:54:23.443
SELECT CONVERT(VARCHAR(8),col_datetime, 112) FROM dbo.TempusFugit 20081026

It's not that the CONVERT() function is complex, but rather the parameters require memorization or constant look-up in order to accommodate your formatting needs.  SQL Server 2008 makes this process so much simpler by use of the date data type.

 

Datetime2 Data Type (New in SQL Server 2008)

SELECT col_datetimecol_datetime2
FROM 
tempusfugit
GO

 

By contrast to the new date data type, the datetime2 data type that is new in Microsoft SQL Server 2008 expands the granularity of the original datetime data type to 100 nanoseconds in the format of  YYYY-MM-DD HH:MM:SS[.fractional seconds].  Storage requirements depend upon the precision of the column, which is declared upon creation.  Precision can be any value between 0 and 7, with the default being 7.

Precision Storage Requirements
< 3 6 bytes
3 or 4 7 bytes
> 5 8 bytes

It is interesting to note that a datetime column and a datetime2(3) column are identical, except in regards to storage requirements.  The datetime field consumes 8 bytes, while the datetime2(3) column requires only 7 bytes of storage:

DECLARE @datetime AS datetime GETDATE()
DECLARE @datetime2 AS datetime2(3GETDATE()

SELECT @datetime AS ValueDATALENGTH(@datetimeAS Storage_Bytes
SELECT @datetime2 AS ValueDATALENGTH(@datetime2AS 
Storage_Bytes

 

I have yet to find documentation explaining this behavior, but if you are dealing with large amounts of datetime data that requires storage of fractional seconds accurate to the 1/1000 of a second it would be more efficient to use the datetime2(3) format in lieu of the standard datetime data type. 

 

Datetimeoffset Data Type (New in SQL Server 2008)

SELECT col_datetimecol_datetimeoffset
FROM 
tempusfugit
GO

The new datetimeoffset data type is useful when dealing with time-zone information.  SQL 2005 data types simply stored date and time; there was no simplified way to standardize times across time zones.  Datetimeoffset combines the datetime2 data type with an additional offset value that is useful for global databases.  In our example with the GETDATE() function we did not include an offset, therefore the result was as you see above.  However, let us assume that we are dealing with a global shipping company's database and they have standardized on Greenwich Mean Time.  To enter a value into their Shipping table for a shipment made in the Eastern time zone in the United States your query may look something like this:

INSERT INTO dbo.Arrivals (ShippingIDArrival_Scan_dt)
VALUES (1234'2008-08-26 11:32:30 -5:00'
)
GO

Cost of storage follows the same guidelines as those for datetime2, with an additional 3 bytes required for storage of the added offset value.  The offset value must fall in the range of +/-00:00 - +/-23:59.

 

Time Data Type (New in SQL Server 2008)

SELECT col_datetimecol_time
FROM 
tempusfugit
GO

 

The time data type is the counterpart to the date data type in that it is the logical outcome of splitting the datetime data type from previous versions of SQL Server.  The format is identical to the time aspect of the datetime data type, with the exception that its precision is adjustable, as is the resulting storage cost from the table below.

Precision Storage Requirements
< 3 3 bytes
3 or 4 4 bytes
> 4 5 bytes

As you can see from our example, we created this particular col_time column as time(7) (the default).  The GETDATE() function returns values as a datetime data type, which has a precision of 3.  The behavior of the time data type is as such that it will not truncate trailing zeros.  This is standard behavior across all date and time data types in SQL Server.  It is important that you understand your data and make the proper decisions when creating the fields in your databases so as not to waste valuable storage space over the course of long term data storage.

 

Next Steps

  • Review databases you have migrated (or plan to migrate) to SQL Server 2008 to determine if any benefit exists for converting your datetime data to one of the new data types available in SQL Server 2008.
  • Review additional tips associated with enhancements to SQL Server 2008 here.
  • Stay tuned to future tips on new data types available in Microsoft SQL Server 2008.
  • Learn more about the CONVERT() function and its use in date and time presentation in this MSSQLTip.


Last Update: 10/30/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, September 02, 2013 - 3:42:13 PM - Manoj Pingle Read The Tip

i wanted to select current date from internet source, Please help me out about this.


Tuesday, May 07, 2013 - 12:09:01 AM - preethi N Read The Tip

need to find date difference of start date & end date the difference should consider both the day difference and time difference in an single value.(integer)

some one give solution


Tuesday, February 26, 2013 - 10:36:58 AM - eqvenzaa Read The Tip

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste 

  • what is the error in this query? can join 2 or three tables in datediff?

SELECt name,

inner join  LibraryMembershipCard on 

 StudentHistoryMain.RollNumber= LibraryMembershipCard.RollNumber

 inner join  CheckoutHistory on

  CheckoutHistory.CardNumber=LibraryMembershipCard.CardNumber,

   DATEDIFF(day, '2012-09-03 00:00:00','2012-09-04 00:00:00'),

 datediff(day, '2012-09-05 00:00:00','2012-09-06 00:00:00'),

 DATEDIFF(day, '2012-08-31 00:00:00','2012-09-10 00:00:00')

 from StudentHistoryMain

e code into a text editor like NotePad before copying the code below to remove the SSMS formatting.


Wednesday, January 02, 2013 - 4:46:44 PM - CodePro Read The Tip

Every date has a time, every time has a date. Pretending otherwise can lead to extremely hard to troubleshoot bugs. These new datatypes give developers the ability to shoot themselves in the foot rather than fix anything that was broken.


Wednesday, June 13, 2012 - 4:06:07 AM - benjamin Read The Tip

thanks for this interesting article.

only a small error in the text regarding the sql server 2005 section

 

"whereas smalldatetime will round up or down to the nearest MINUTE"

instead of

"whereas smalldatetime will round up or down to the nearest second" 

 

cheers


Wednesday, February 23, 2011 - 10:21:54 AM - Russ Bell Read The Tip

Very nice and clear explanation of the available date and time options in SQL Server 2008.  I would suggest adding information about the range of dates that can be put into the various formats because that may be an important criterion for selecting the appropriate data type.  For example, SMALLDATETIME accommodates dates in the limited range of 1900-01-01 through 2079-06-06, whereas the normal DATETIME handles 1753-01-01 through 9999-12-31.  If you need to support dates prior to 1753, the new datatypes in SQl 2008 can be helpful as DATE, DATETIME2, and DATETIMEOFFSET handle the full range of years 0001 through 9999.  Dates that previously had to be handled as strings can now be a real date data type with full date arithmetic.


Thursday, October 30, 2008 - 10:05:12 AM - SGT49 Read The Tip

 Why was this perceived to be a problem with SS2005?  I use smalldate datatypes without including the time component.  The default is 00:00.  When used  in an application, I use an appropriate format in the control (in .Net apps) and no time is shown.  Also, with date math there is no problem either.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.