When you use convert datetime 127 with Stuff, you need to be careful to remove the character's start position

When you use convert datetime 127 with Stuff, you need to be careful to remove the character's start position

Convert Datetime to 127 is in the format yyyy-mm-ddthh:mi:ss.mmm

MSDN gives an important note:

When the value of milliseconds (MMM) is 0, the milliseconds value was not displayed. For example, the value ' 2012-11-07t18:26:20.000 is displayed as ' 2012-11-07t18:26:20 '.

Example

Declare @dt1 datetimeDeclare @dt2 datetimeSet @dt1='2015-01-02 01:23:45.678'Set @dt2='2015-01-02 01:23:45.000'Select CONVERT(VARCHAR( -),@dt1,127),Len(CONVERT(VARCHAR( -),@dt1,127)),        CONVERT(VARCHAR( -),@dt2,127),Len(CONVERT(VARCHAR( -),@dt2,127))

If stuff (convert (varchar), @datetime, 127, 20,4), an "unexpected" result appears

Declare @dt1 datetimeDeclare @dt2 datetimeSet @dt1='2015-01-02 01:23:45.678'Set @dt2='2015-01-02 01:23:45.000'Select CONVERT(VARCHAR( -),@dt1,127),Len(CONVERT(VARCHAR( -),@dt1,127)),        CONVERT(VARCHAR( -),@dt2,127),Len(CONVERT(VARCHAR( -),@dt2,127)),        STUFF(CONVERT(VARCHAR( -),min(@dt1),127), -,4,"') ,        STUFF(CONVERT(VARCHAR( -),min(@dt2),127), -,4,"')

The reason is that the stuff function returns NULL,MSDN gives an explanation

STUFF (character_expression, start, length, replacewith_expression)

If the start position or the length is negative, or if the starting position is larger than length of the first string , a null string is returned. If The start position is 0, a null value of is returned. If The length to delete is longer than the first string, it's deleted to the first character in the first string.

When you use convert datetime 127 with Stuff, you need to be careful to remove the character's start position