Rounding milliseconds SQL Server 2000

Question

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

http://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions — Jodrell, Jan 19 '15 at 14:26
Yes. I copied that question since it is not working on my sql version. — Elish Torres, Jan 19 '15 at 14:29
see the fiddle, it works, http://sqlfiddle.com/#!3/d41d8/42581 — Jodrell, Jan 19 '15 at 14:41

Donal · Accepted Answer · 2015-01-19T15:15:00.127

1

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

edited Jan 19 '15 at 15:15

answered Jan 19 '15 at 14:26

Donal

31,121
10
63
72

*`DATETIME2`: from SQL Server 2008 and up* – Radu Gheorghiu Jan 19 '15 at 14:26
I'm getting this error. Line 1: Length or precision specification 0 is invalid. – Elish Torres Jan 19 '15 at 14:27
@ElishTorres what version of SQL Server are you using? – Donal Jan 19 '15 at 14:28
Microsoft SQL Server 2008. – Elish Torres Jan 19 '15 at 14:28
@ElishTorres, then you need to upgrade to a database from this decade or, add a tag to your question. – Jodrell Jan 19 '15 at 14:31
I copy pasted the code above on my sql and got that error. – Elish Torres Jan 19 '15 at 14:40
@Donal oopps. It's not 2008. It's 2000. Sorry. – Elish Torres Jan 19 '15 at 14:45
@MikaelEriksson It's 2000. I'm sorry. – Elish Torres Jan 19 '15 at 14:46
@ElishTorres ok, I have added the SQL that should work with SQL Server 2000, I don't have SQL Server 2000 - so I could not test it. – Donal Jan 19 '15 at 15:15

score 0 · Answer 2 · answered Jan 19 '15 at 15:20

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

This answer that will give overflow errors if you replace the value of the_date with getdate() — t-clausen.dk, Jan 19 '15 at 15:31
@t-clausen.dk correct. But that is not part of the original requirement by the OP. If this is an issue for you then you can tweak the value used in the DateDiff() comparison from 0 (=1900-01-01) to something more appropriate for your dataset e.g. 2000-01-01. — gvee, Jan 19 '15 at 15:45

Rounding milliseconds SQL Server 2000

2 Answers2