Search code examples
sqlsql-serversql-server-2000

Rounding milliseconds SQL Server 2000

In Microsoft SQL Server 2000, I have this data.

1900-01-01 00:10:10.830
1900-01-01 00:10:10.430

From the above column, I want to select the datetime and round off the milliseconds, in order to get the below output

1900-01-01 00:10:11
1900-01-01 00:10:10

Thanks in advance

Solution

  • For SQL Server 2008 and above, you can do use DATETIME2. DATETIME2 is available in SQL Server 2008 and above - for more info see here:

    SELECT CAST('1900-01-01 00:10:10.830' AS DATETIME2(0));
SELECT CAST('1900-01-01 00:10:10.430' AS DATETIME2(0));

    Confirmed Output

    For earlier version of SQL Sever, for example SQL Server 2000. You can do something like this:

    SELECT DATEADD(ms, -DATEPART(ms, DATEADD(ms, 500, CAST('1900-01-01 00:10:10.830' AS DATETIME))) , DATEADD(ms, 500, CAST('1900-01-01 00:10:10.830' AS DATETIME)));
SELECT DATEADD(ms, -DATEPART(ms, DATEADD(ms, 500, CAST('1900-01-01 00:10:10.430' AS DATETIME))) , DATEADD(ms, 500, CAST('1900-01-01 00:10:10.430' AS DATETIME)));