CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Elish Torres
Elish Torres

Reputation: 35

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

Upvotes: 2

Views: 662

Answers (2)

gvee
gvee

Reputation: 17161

SELECT *
     , DateAdd(ss, rounded_second, round_down_seconds) As result
FROM   (
        SELECT *
             , Round(nanoseconds / 1000.0, 0) As rounded_second
        FROM   (
                SELECT the_date
                     , DatePart(ms, the_date) As nanoseconds
                     , DateAdd(ss, DateDiff(ss, 0, the_date), 0) As round_down_seconds
                FROM   (
                        SELECT '1900-01-01 00:10:10.830' As the_date
                        UNION ALL 
                        SELECT '1900-01-01 00:10:10.430'
                       ) As x
               ) As y
       ) As z

I've split out each step to be as clear as possible.

If you want a single liner:

SELECT the_date
     , DateAdd(ss, Round(DatePart(ms, the_date) / 1000.0, 0), DateAdd(ss, DateDiff(ss, 0, the_date), 0)) As result
FROM   (
        SELECT '1900-01-01 00:10:10.830' As the_date
        UNION ALL 
        SELECT '1900-01-01 00:10:10.430'
       ) As x

Upvotes: 0

Donal
Donal

Reputation: 32713

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)));

Upvotes: 1

Related Questions