SQL Server 2008 Date and Time Data Types

By:   |   Comments (9)   |   Related: > Dates


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?


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]  

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_datetime, col_smalldatetime, col_date, col_datetime2, col_datetimeoffset, col_time)  

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_datetime, col_smalldatetime 
FROM dbo.tempusfugit 

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_datetime, col_date 
FROM tempusfugit 

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_datetime, col_datetime2 
FROM tempusfugit

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(3) = GETDATE() 

SELECT @datetime AS Value, DATALENGTH(@datetime) AS Storage_Bytes 
SELECT @datetime2 AS Value, DATALENGTH(@datetime2) AS 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_datetime, col_datetimeoffset 
FROM tempusfugit

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 (ShippingID, Arrival_Scan_dt) 
VALUES (1234, '2008-08-26 11:32:30 -5:00')

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_datetime, col_time 
FROM tempusfugit

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, December 24, 2018 - 3:44:13 PM - Philip van Gass Back To Top (78548)

Thanks for this tip but I am confused by the DATETIMEOFFSET data type. In your example you have a shipping company that has standardized on GMT. Must I assume that a shipment made on the date  '2008-08-26 11:32:30 -5:00' is actually showing a time which is 5 hours ahead of the local time of the shipping company ? I live in a place which is 2 hours ahead of GMT, but if I convert the local date/time to a DATETIMEOFFSET data type then I get +00:00 added on to the correct local time. Why does it not show +2:00 ? 

Saturday, November 12, 2016 - 7:17:05 AM - Meena M Back To Top (43753)

I want to store the entry and exit time of employee than how can i get the result to filter an given particular time an employee is present or not



Monday, September 2, 2013 - 3:42:13 PM - Manoj Pingle Back To Top (26580)

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

Tuesday, May 7, 2013 - 12:09:01 AM - preethi N Back To Top (23751)

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 Back To Top (22431)

*** 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


   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 2, 2013 - 4:46:44 PM - CodePro Back To Top (21237)

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 Back To Top (17959)

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" 



Wednesday, February 23, 2011 - 10:21:54 AM - Russ Bell Back To Top (13015)

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 Back To Top (2113)

 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.

get free sql tips
agree to terms