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.

Also read SQL Server: DateTime vs DateTime2

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 www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan