Sql-server – Milliseconds to datetime

sql serversql-server-2008

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?

Best Answer

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.

Related Solutions

SQL Server – Convert varchar Column to datetime

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME, 
    REPLACE(LEFT(varchar_col, 11), '/', ' ') 
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.

Sql-server – Convert datetime to europe default without time

Instead of your hack with replace, why not just cast to the right data type? This doesn't need to be nvarchar and the only reason it contains time is because you included too many characters. You can of course apply your replace here as well (or you could do that at the client).

SELECT CONVERT(CHAR(11),GETDATE(),13)
UNION ALL
SELECT CONVERT(CHAR(11), DATEADD(DAY, 10, GETDATE()), 13);

Results:

27 Aug 2012
06 Sep 2012

Best Answer

Related Solutions

SQL Server – Convert varchar Column to datetime

Sql-server – Convert datetime to europe default without time

Related Question