24

I have this time-duration: 00:00:23.323 I want to convert it in sql to milliseconds.

EDIT:// I tried this but it isn't very nice:

SELECT  (DATEPART(hh,'12:13:14.123') * 60 * 60 * 1000)
SELECT  (DATEPART(n,'12:13:14.123') * 60 * 1000)
SELECT  (DATEPART(s,'12:13:14.123') * 1000)
SELECT  DATEPART(ms,'12:13:14.123')

How does it work?

Thanks for your answers.

Lingo
  • 580
  • 2
  • 7
  • 26

3 Answers3

35

Use DATEDIFF:

SELECT DATEDIFF(MILLISECOND, 0, '00:00:23.323')

Result:

23323
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
3

You can use datepart function. like this 

select DATEPART(MILLISECOND,GETDATE())+DATEPART(second,getdate())*1000
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
  • And this is for which dbms product? (No one specified in question, and DATEPART is product specific...) – jarlh Sep 16 '15 at 07:39
2

I got it, it isn't the nice way but it works:

SELECT (DATEPART(hh,'00:00:23.323') * 60 * 60 * 1000) + (DATEPART(n,'00:00:23.323') * 60 * 1000) + (DATEPART(s,'00:00:23.323') * 1000) + DATEPART(ms,'00:00:23.323') AS 'DurationInMillis'
Lingo
  • 580
  • 2
  • 7
  • 26
  • The [answer](http://stackoverflow.com/a/32602860/1175077) by t-clausen.dk is much better... – jpw Sep 16 '15 at 08:03
  • 1
    I know the OP's only asking about "time" durations, but for what it's worth, the pattern in @Lingo's solution scales better to durations >= 1 day than @t-clausen.dk's, because `DateDiff` returns an `Int`, which means it tops out at ~24 days worth of milliseconds and ~67 yrs. of seconds. `DateDiff_Big` (which returns a `BigInt`) is not available prior to SQL Server 2016. – Tom Mar 09 '17 at 17:56