Group by and DateAdd and simple computations SQL Server

Question

Here is my table and query

Table 1

AsOfDate                  Value
2015-06-06 11:08:21.247     5 
2015-06-06 12:08:21.247     7 
2015-06-07 10:08:21.247     8 
2015-06-08 11:08:21.247     8      
2015-06-08 10:09:21.247     9 

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate])) as DateOnly ,Min([Value])
FROM Table1 group by DateOnly

I am getting an error message "Invalid column name 'DateOnly'."

What would be the best way to accomplish it?

Answer 1

Change your query from :

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate])) as DateOnly ,Min([Value])
FROM Table1 group by DateOnly

To

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate])) as DateOnly ,Min([Value])
FROM Table1 group by DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate]))

the alias DateOnly is known to thie level of query ( only to top of it)

Answer 2

You have to group by DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate])). Unfortunately you cannot use alias names in group by clauses.

Answer 3

Here DateOnly is a column alias. So use the expression itself in GROUP BY like

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate])) as DateOnly ,Min([Value])
FROM Table1 
group by DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate]))

Answer 4

You have used not the best way to cut time part from datetime data. If you use SQL Server version which supports date data type simply use CAST(AsOfDate as date) or you can use this: CAST(CAST(AsOfDate as int) as datetime)

So the query can be something like this:

SELECT CAST(AsOfDate as date) as DateOnly ,Min([Value])
FROM Table1
GROUP BY CAST(AsOfDate as date)