Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

DECLARE @mdate AS DATETIME2(7) 

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

1 Answer 1

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

 
discard

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.