I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this:


SET @mdate = '2012-03-15 10:13:27.5437431'

SELECT LEFT(REPLACE(REPLACE(CONVERT(VARCHAR ,@mdate ,114),':',''),'.',''),9)

Is there a better approach than doing this ugly replaces/convert/left?

I need this to join to a DimTime dimension whose key is in hhmmssfff format.

1 Answer 1

  1. Please don't use varchar without length.
  2. Please use semi-colons as statement terminators.

  3. I don't think there is a more covert way to do this. Here is how I would do it (14 characters shorter, ignoring the semi-colon):

    DECLARE @mdate AS DATETIME2(7);
    SET @mdate = '2012-03-15 10:13:27.5437431';
    SELECT REPLACE(REPLACE(CONVERT(TIME(3),@mdate),'.',''),':','');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.