Datetime precision lost with default conversion to varchar

Or as I like to call it, where'd my seconds go?

I wish I wasn't learning this nugget: the default conversion from datetime to varchar results in a precision to the minute. Seconds, milliseconds? Sure hope you didn't need those, because they're gone.

A less than talented person designed managed to bang on enough keys to write a proc that takes as a parameter this datetime value. However, they declared it as a varchar(50). No problem, I think, it's just because he was too lazy to figure out how to cast it properly when he uses it with all this dynamic sql he's building in this proc. While I'm sure the latter portion is true, a subtle error was introduced by the implicit conversion, we lost the seconds and milliseconds for this timestamp. Which might have not been so bad, were that period not used in 8 other tables to group transactions together.

SELECT 
    cast(current_timestamp AS varchar(50)) AS default_cast
,   convert(varchar(50), current_timestamp, 100) AS explicit_convert
,   current_timestamp AS default_select_display

default_cast	explicit_convert	default_select_display
Mar 15 2011 10:41PM	Mar 15 2011 10:41PM	2011-03-15 22:41:21.960

The format for default_select_display is the native display for a datetime value. See those last five numbers? That's the time between 00.000 seconds and 59.997 seconds which is now lost to the ether when you cast or convert to character data type without specifying that you want it in a sane format, like 121 (which is the lossless format used for default_select_display).

The really sad this is that rather than fix this the right way, which would involve rewriting the "proc with cursor which calls second proc which calls the same non-inline table valued function twice with identical parameters to build a dynamic SQL statement just to do a simple insert" with a nice set-based method. Instead, this works "good enough" until I get the time to clean it up proper.

SET @bloodyStupidHack = convert(varchar(50), @rptg_prd_dt, 121)

</rant>

BOL Reference

CAST and CONVERT