August 22, 2011

SQL Server: Limit Millisecond Part in DateTime2 Datatype

Till version 2005, SQL Server supported only the datetime datatype which stored date and time with milliseconds upto three digits. However SQL Server 2008 supports separate data types DATE and TIME as well DATETIME2 datatype which stores milliseconds upto 7 digits.
You can specify the length for DATETIME2 so you can limit the number of digits
displayed for millisecond part.

You can find out the result from the output of the following queries:

select cast(getdate() as datetime2(2))
select cast(getdate() as datetime2(5))
select cast(getdate() as datetime2)

The first query limits milliseconds to 2 digits, second query limits it to 5 digits and last query will show 7 digits, as milliseconds, as no length is specified.

datetime2 millisecond

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at and a moderator at His T-sql blog is at



1 Response to "SQL Server: Limit Millisecond Part in DateTime2 Datatype"
  1. --CELKO-- said...
    September 15, 2011 at 8:40 AM

    We alaso have the ANSI/ISO Standard SQL CURRENT_TIMESTAMP instead of getdate(). The old getdate() goes back to UNIX and Sybase.


Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions