Solved

DATEPART with milliseconds, how to get leading zero

Posted on 2008-10-02
4
935 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 48
T-SQL: Episode III - Revenge of The Dude 24 53
Query Syntax 17 34
How to search for strings inside db views 4 27
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

770 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