• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1004
  • Last Modified:

DATEPART with milliseconds, how to get leading zero

I am trying to pull the milliseconds from a datetime column using CAST(DATEPART(millisecond, qual.time)AS VARCHAR(3))

I need the first two digits of the milliseconds with a leading zero.

Right now if the milliseconds is .090  I am getting .90 back which is incorrect.   I have tried casting to an INT but i get an error that "String 2:2 cannot be cast to an int".  I believe this is from the data in the database which is 1900-01-01 02:02.090  .  For some reason the datepart function and the cast are attemping to convert the entire date instead of just the milliseconds.
0
skykingjwc
Asked:
skykingjwc
  • 2
1 Solution
 
chapmandewCommented:
I am pretty sure 90 is correct, but here is how you can get all 3 digits

declare @x varchar(50)
set @x = '1900-01-01 02:02.090'
select substring(@x, charindex( '.', @x)+1, len(@x))
0
 
skykingjwcAuthor Commented:
9 tenths of a second is not the same as 9 hundredths of a second.
If you read the second line in the question, I wanted the first two digits (tenths and hundredths) after the decimal.
0
 
chapmandewCommented:
ok...

declare @x varchar(50)
set @x = '1900-01-01 02:02.090'
select left(substring(@x, charindex( '.', @x)+1, len(@x)), 2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now