How do I rounding DateTime to 6 characters after the second?

I have a table called Test and I have a column called Date w开发者_如何学编程hich is of Datetime datatype. My Requirement is to get the values of the Date in this format: YYYY-MM-DD hh:mm:ss.123456.

But I am getting values like:

Date
2011-03-09 11:35:03.890

So I changed the datatype of Date column to Datetime2 so Now I am getting the value like :

Date
2011-03-09 11:35:03.8900000

The problem is I want only 6 characters after that seconds but I am getting seven. Can anyone help me on this?

Example:

If the Date has value of 2011-03-07 11:35:03.1234567 then it should round of the value to

2011-03-07 11:35:03.1234570 and from that I want only six characters after that seconds so

I want some thing like this:

Date
2011-03-07 11:35:03.123457

DECLARE @d DATETIME2 = '2011-03-07 11:35:03.1234567'

SELECT CAST(CAST (@d AS DATETIME2(6)) AS CHAR(26))

Returns

2011-03-07 11:35:03.123457

You need to cast the precision to get the rounding and then trucate it with by limitting the characters. See the following example:

smalldatetime  2007-05-08 12:35:00
datetime       2007-05-08 12:35:29.123
datetime2      2007-05-08 12:35:29.1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15

Try:

CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(6)) AS CHAR(26)

Reference MSDN.