Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 2 down vote accepted
  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),'.',''),':','');
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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