Solved

DATEPART with milliseconds, how to get leading zero

Posted on 2008-10-02
4
932 Views
Last Modified: 2009-01-05
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
Comment
Question by:skykingjwc
  • 2
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22626421
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
 
LVL 4

Author Comment

by:skykingjwc
ID: 22632973
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22633092
ok...

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now