0

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?

Improve this question
7
  • What do the miliseconds relate to? Number of ms after current time?
    – Martin Smith
    Commented Mar 14, 2012 at 9:57
  • @MartinSmith no after default value of datetime
    – M.S.Nayak
    Commented Mar 14, 2012 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
    Commented Mar 14, 2012 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
    Commented Mar 14, 2012 at 10:19
  • 4
    And why not just store the number of hours as an int rather than a nonsensical datetime?
    – Martin Smith
    Commented Mar 14, 2012 at 10:31

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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