A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, March 16, 2011

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.

    cast(current_timestamp AS varchar(50)) AS default_cast
,   convert(varchar(50), current_timestamp, 100) AS explicit_convert
,   current_timestamp AS default_select_display
Mar 15 2011 10:41PMMar 15 2011 10:41PM2011-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)

BOL Reference


1 comment:

Anonymous said...

Great article, I was confused about this myself and you're the only link on google with good info on the subject.

It is worth mentioning, however, that once a GETDATE() or CURRENT_TIMESTAMP is casted to VARCHAR it is no longer a DateTime variable and therefor can't use the DateTime functions on it. I know this may seem obvious but to the naked eye there is no difference between "2012-09-05 13:46:13.593" (string) and "2012-09-05 13:46:13.593" (DateTime).