SQL Server: GROUP BY datetime without ticks

I have a query, that returns all entries, with unique datetime with ticks:

select t.date, count(*) as Count
    from table t
    group by t.date having count(*) = 1

I need a query that will return entries with unique datetime without ticks. To get datetime without ticks I'm using:

select CONVERT(VARCHAR(19), t.date, 120), count(*) as Count
from table t

So I'm expecting to use query:

select CONVERT(VARCHAR(19), t.date, 120), count(*) as Count
    from table t
    group by CONVERT(VARCHAR(19), t.date, 120) having count(*) = 1

But it throws an error:

Column "table.date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Do you have any idea what query should I use?

Answers


This works fine:

select convert(varchar(19), t.date, 120), count(*) as count
from table t
group by convert(varchar(19), t.date, 120) 
having count(*) = 1
order by convert(varchar(19), t.date, 120) 

There are multiple possible datetime values in each group so instead of

ORDER BY t.date

you could use, for example

ORDER BY MIN(t.date)

to avoid this error.


Need Your Help

DateTime parse bug in WinRT Windows 8 RTM?

c# datetime windows-8 windows-runtime .net-4.5

Having upgraded to Windows 8 RTM, some date time parsing code that I had has stopped working. I've replicated this using a standard console app:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.