I have this statement

EXEC [spProject_Update] @ProjectID = 496
,@Name = 'Hangman Bollywood iPhone'
,@Description = ''
,@EstimateTime = 2674800000

I will get milliseconds in @EstimateTime.
How can I convert this to datetime in SQL Server?

  • What do the miliseconds relate to? Number of ms after current time? – Martin Smith Mar 14 '12 at 9:57
  • @MartinSmith no after default value of datetime – M.S.Nayak Mar 14 '12 at 10:02
  • Doesn't make any sense. There is no default value of datetime. If you are referring to 1 Jan 1900 then 2674800000 milliseconds is only 743 hours so doesn't seem likely. – Martin Smith Mar 14 '12 at 10:07
  • 2
    So you are using 1 Jan 1900 as a reference date? Why? DATEADD(MILLISECOND, 2674800000,0) fails with an overflow error. DATEADD(SECOND, 2674800,0) works but I presume that you aren't actually going to be using dates in 1900 so that will overflow too. Why not just pass in as datetime? – Martin Smith Mar 14 '12 at 10:19
  • 4
    And why not just store the number of hours as an int rather than a nonsensical datetime? – Martin Smith Mar 14 '12 at 10:31

DATETIME is used to store an instant ("point in time") at a particular granularity (approx 300 milliseconds IIRC).

The value you wish to store is an interval (or is it a duration?) in milliseconds. SQL Server lacks an interval data type.

While both are temporal data they have quite different requirements. I suggest you use an numeric column (e.g. INTEGER) to store the interval and use metadata (e.g. an appropriate name) to convey the fact the granularity is one millisecond.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.