vendredi 29 janvier 2016

T-SQL Distinct Close Dates

Using SQL Server 2005 I am selecting DATETIME from a set of two tables using a UNION. Many of these are very close together: e.g:

2016-01-29 10:28:57.540
2016-01-29 10:28:57.647
2016-01-29 11:23:18.193
2016-01-29 11:23:18.240

In this example I would like to get back just

2016-01-29 10:28:57.000  
2016-01-29 11:23:18.000

This is easy using some date/conversion functions to remove the ms part. However if we get the following:

2016-01-29 10:18:58.105
2016-01-29 10:18:57.952
2016-01-29 11:13:18.193
2016-01-29 11:13:18.240

I will get 3 datetimes when I only want 2:

2016-01-29 10:18:58.000
2016-01-29 10:18:57.000
2016-01-29 11:13:18.000

Instead of:

2016-01-29 10:18:58.000
2016-01-29 11:13:18.000

As 2016-01-29 10:18:58.105 and 2016-01-29 10:18:57.952 are less than a second apart.

So the question is how can I group together DATETIME values which are within a second of each other?

Aucun commentaire:

Enregistrer un commentaire