time to ms

Question

Post reply

time to ms

RightOnTarget

Hall of Fame

Points: 3041
More actions
September 4, 2012 at 9:52 am

#281072

Hi all,
I have a column of "time" data type. I need to extract it as milliseconds. Is there a function I can use to do that in SS2K8?
Here are some examples of the data stored:
0:12:14:124
0:08:16:361
0:08:57:705
Thanks,
Eugene

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

~~Try DATEPART(ms, YourTimeValue)~~

Sorry, I need more coffee, I'll come back.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Try thie:

with testdata as ( -- this cte is only used to provide the test data for the code below

select

cast(stuff(dt.MyTime,8,1,'.') as time) MyTime

from

(values ('0:12:14:124'),('0:08:16:361'),('0:08:57:705')) dt(MyTime)

)

select

datediff(ms,cast('00:00:00' as time), MyTime)

from

testdata;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Just to give a different answer, but I'd stay with Lynn's function, as it's easier to read/write.

DECLARE @test-2 TABLE( Mytime time)

INSERT INTO @test-2 VALUES ('0:12:14:124'),('0:08:16:361'),('0:08:57:705')

SELECT (DATEPART( HH, Mytime) * 3600000)

+ (DATEPART( MI, Mytime) * 60000)

+ (DATEPART( S, Mytime) * 1000)

+ DATEPART( MS, Mytime)

FROM @test-2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

After seeing Luis's code, retested mine without the stuff, it still works:

with testdata as ( -- this cte is only used to provide the test data for the code below

select

cast(dt.MyTime as time) MyTime

from

(values ('0:12:14:124'),('0:08:16:361'),('0:08:57:705')) dt(MyTime)

)

select

datediff(ms,cast('00:00:00' as time), MyTime)

from

testdata;

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 5

Thanks for replies,

Trying to use your last solution in the query and get an error:

--------------

Msg 241, Level 16, State 1, Line 9

Conversion failed when converting date and/or time from character string.

----------------

select 'a', 'b', datediff(ms,cast('00:00:00' as time),cast(time_column as time)) time_ms

from T

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

eugene.pipko (9/4/2012)
Thanks for replies,
Trying to use your last solution in the query and get an error:
--------------
Msg 241, Level 16, State 1, Line 9
Conversion failed when converting date and/or time from character string.
----------------
select 'a', 'b', datediff(ms,cast('00:00:00' as time),cast(time_column as time)) time_ms
from T

Check your data. About all I can say since I can't see it from here.

Lowell SSC Guru Points: 323518 More actions · Answer 7

i think this will do what you wanted:

With MyCTE (TheTime)

AS

(

SELECT CONVERT(time,'0:12:14:124') UNION ALL

SELECT '0:08:16:361' UNION ALL

SELECT '0:08:57:705'

)

SELECT *,DATEDIFF(millisecond,cast('00:00:00' as time), TheTime)

From myCTE

Edit:

late to the party; that's what i get for stepping away from my desk; you already got some fine answers before mine.,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Both do the same for me:

with testdata as ( -- this cte is only used to provide the test data for the code below

select

cast(dt.MyTime as time) MyTime

from

(values ('0:12:14:124'),('0:08:16:361'),('0:08:57:705')) dt(MyTime)

)

select

datediff(ms,cast('00:00:00' as time), MyTime)

from

testdata;

with testdata as ( -- this cte is only used to provide the test data for the code below

select

convert(time, dt.MyTime) MyTime

from

(values ('0:12:14:124'),('0:08:16:361'),('0:08:57:705')) dt(MyTime)

)

select

datediff(ms,cast('00:00:00' as time), MyTime)

from

testdata;

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 9

Do you think I am receiving following message because my data is stored in nvarchar(max) column?

--------------

Msg 241, Level 16, State 1, Line 9

Conversion failed when converting date and/or time from character string.

----------------

select 'a', 'b', datediff(ms,cast('00:00:00' as time),cast(time_column as time)) time_ms

from T

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

eugene.pipko (9/4/2012)
Do you think I am receiving following message because my data is stored in nvarchar(max) column?
--------------
Msg 241, Level 16, State 1, Line 9
Conversion failed when converting date and/or time from character string.
----------------
select 'a', 'b', datediff(ms,cast('00:00:00' as time),cast(time_column as time)) time_ms
from T

The error message is telling you that there is data that won't convert from character format to date and/or time format.

Lowell SSC Guru Points: 323518 More actions · Answer 11

defintiely the problem.

do a SELECT * FROM T WHERE isdate(time_column) = 0

those are the values that cannot be converted, and raise an error.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 12

You're right as I see the following values:

2 days 23:28:45 .703

-4:0-57:0-26:00-703

when running SELECT * FROM T WHERE isdate(time_column) = 0