Datetime grouping fails when converting

1

Hi. SQL 2008R2
I have 2 dates like 2018-05-18 21:04:44.717 and 2018-05-18 21:04:44.917
I want to get rid of the seconds milliseconds part .
Doing this convert(varchar,thedate,120) as mydate
and grouping by thedate as convert(varchar,thedate,120) will group it but if there is a second difference then it will not group it.

So I would need something like this (that will not work but is to give you a clue)
select convert(varchar,thedate,120) as mydate
group by convert(varchar,thedate,112) -- yyyymmdd

Of course this will complain.

I don't want to go to overcomplicated solutions with tables grouping tables grouping tables.
Is there a simple solution?
Thanks.

2

declare @start datetime
set @start = '2018-05-18 21:04:44.717'

select
@start

,convert(datetime,
convert(varchar(12),cast(floor(convert(float,@start)) as datetime),112)
+' '+
convert(varchar(8),@start,108)) _mili_secs

,convert(datetime,
convert(varchar(12),cast(floor(convert(float,@start)) as datetime),112)
+' '+
convert(varchar(5),@start,108)) no_mili_secs

3

This is complicated and possibly killing performance.
I was suggested to use a max on the first select.
Isn't that more rumbust?
Thanks.

4

instead of using:-

select convert(varchar,thedate,120) as mydate

Use this :-

select convert(datetime,convert(varchar(12),cast(floor(convert(float,thedate)) as datetime),112) +' '+ convert(varchar(5),thedate,108)) as mydate

1 Like
5

select dateadd(minute, datediff(minute, 0, thedate), 0), ...
from ...
group by dateadd(minute, datediff(minute, 0, thedate), 0)

6

If you want to group at the seconds - why not just convert to smalldatetime. If you want to round up to the nearest minute - then use Scott's version.

1 Like
7

That code should truncate, not round, which is what you want to do. That keeps the value within whatever minuted it started in. For example, if the time is 1:59:59PM, the above code should include it in 1:59PM.

select dateadd(minute, datediff(minute, 0, thedate), 0)
from ( values('13:59:59.997') ) data(thedate)

1 Like
8

Thanks