How to group by date irrespective of time

[anonymous]

Hello,
I have a table which has a date column. The values in the date column are along with time
i.e., 2/1/2008 12:00:00 PM
2/1/2008 2:13:00 PM
2/3/2008 4:00:00 AM
3/1/2008 1:00:00 PM
3/1/2008 4:00:00 PM
3/3/2008 2:18:00 AM
3/3/2008 5:00:00 PM
3/3/2008 7:08:12 PM

I want to group the data based on dates irrespective of time. i,e., i want the results to be as follows


Date Count
2/1/2008 2
2/3/2008 1
3/1/2008 2
3/3/2008 3

How do i query the table using T-SQL to get the above reults..I tried using different date formats, but still i am not getting the expected results..

[amitpatel66]

Hello,
I have a table which has a date column. The values in the date column are along with time
i.e., 2/1/2008 12:00:00 PM
2/1/2008 2:13:00 PM
2/3/2008 4:00:00 AM
3/1/2008 1:00:00 PM
3/1/2008 4:00:00 PM
3/3/2008 2:18:00 AM
3/3/2008 5:00:00 PM
3/3/2008 7:08:12 PM

I want to group the data based on dates irrespective of time. i,e., i want the results to be as follows


Date Count
2/1/2008 2
2/3/2008 1
3/1/2008 2
3/3/2008 3

How do i query the table using T-SQL to get the above reults..I tried using different date formats, but still i am not getting the expected results..

Try this:

 
SELECT trunc(dat), COUNT(*) FROM table1 GROUP BY trunc(dat)
 
 

[anonymous]

This query works in Oracle, but i want it in MS SQL.

[ck9663]

Hello,
I have a table which has a date column. The values in the date column are along with time
i.e., 2/1/2008 12:00:00 PM
2/1/2008 2:13:00 PM
2/3/2008 4:00:00 AM
3/1/2008 1:00:00 PM
3/1/2008 4:00:00 PM
3/3/2008 2:18:00 AM
3/3/2008 5:00:00 PM
3/3/2008 7:08:12 PM

I want to group the data based on dates irrespective of time. i,e., i want the results to be as follows


Date Count
2/1/2008 2
2/3/2008 1
3/1/2008 2
3/3/2008 3

How do i query the table using T-SQL to get the above reults..I tried using different date formats, but still i am not getting the expected results..

try:

select convert(varchar(10), YourDateColumn,101), count(*)
from YourTable 
group by convert(varchar(10), YourDateColumn,101)
 

-- CK

[anonymous]

This query is working fine.
I want to order the result based on dates..so i added
order by convert(varchar(10), Transaction_date_time,101)
at the end of the query.
When i query i get the results as follows which is not right in order
01/02/2008 1
01/18/2008 3
01/22/2008 1
04/07/2007 2
04/10/2007 1
04/13/2007 2
04/19/2007 2
05/04/2007 1
05/05/2007 3
05/08/2007 2
05/25/2007 1
05/31/2007 2
06/16/2007 1
06/19/2007 2
06/25/2007 1
06/26/2007 1
07/05/2007 1
07/12/2007 1
07/23/2007 1
07/24/2007 1
07/27/2007 2
07/31/2007 2
08/02/2007 1
08/09/2007 1
08/13/2007 1
08/20/2007 1
08/21/2007 1
08/23/2007 1
08/24/2007 2
08/30/2007 1
09/08/2007 2
09/10/2007 1
09/13/2007 1
09/18/2007 1
09/24/2007 2
09/27/2007 2
10/04/2007 1
10/05/2007 1
10/09/2007 1
10/12/2007 2
10/16/2007 1
10/25/2007 3
10/31/2007 2
11/02/2007 1
11/03/2007 1
11/05/2007 1
11/23/2007 1
11/26/2007 1
12/04/2007 1
12/18/2007 2
12/19/2007 2.
How do i solve it..Actually i want to get the latest 10 dates from the table. i.e.,
i want results as
1/22/2008 1
1/18/2008 3
1/2/2008 1
12/19/2007 2
12/18/2007 2
12/4/2007 1
11/26/2007 1
11/23/2007 1
11/5/2007 1
11/3/2007 1

So if i add Top 10, then i get
01/02/2008 1
01/18/2008 3
01/22/2008 1
04/07/2007 2
04/10/2007 1
04/13/2007 2
04/19/2007 2
05/04/2007 1
05/05/2007 3
05/08/2007 2 which is incorrect..Please help me in solving this..

[ck9663]

use ordinal position of the columns

ORDER BY 1 

where 1 means the first column. So if the date is not the first column, change it accordingly.

-- CK

[anonymous]

use ordinal position of the columns

ORDER BY 1 

where 1 means the first column. So if the date is not the first column, change it accordingly.

-- CK

This is the query which i am using
select top 10 convert(varchar(10), date_time,101), count(*)
from My_Table
group by convert(varchar(10), date_time,101)
order by 1

But still i am getting the same results as shown in my previous message

[ck9663]

This is the query which i am using
select top 10 convert(varchar(10), date_time,101), count(*)
from My_Table
group by convert(varchar(10), date_time,101)
order by 1

But still i am getting the same results as shown in my previous message

Your result is sorted based on date. If you mean you want it descending (latest on top), add the DESC keyword.

-- CK

[anonymous]

Your result is sorted based on date. If you mean you want it descending (latest on top), add the DESC keyword.

-- CK

If i add desc, it gives me result as follows
01/02/2008 1
01/18/2008 3
01/22/2008 1
04/07/2007 2
04/10/2007 1
04/13/2007 2
04/19/2007 2
05/04/2007 1
05/05/2007 3
05/08/2007 2

I think it is not ordering based on the date completely. It is ordering based on month irrespective of year..How do i solve it?

[ck9663]

Sorry, I missed the year part. Since you converted the date to varchar, it sort it like a string. Try a:

ORDER BY DATE_TIME

instead. I believe it will sort it even if it's not on your SELECT list.

-- CK

[anonymous]

Sorry, I missed the year part. Since you converted the date to varchar, it sort it like a string. Try a:

ORDER BY DATE_TIME

instead. I believe it will sort it even if it's not on your SELECT list.

-- CK

When i changed order by Date_time, it gave me a error saying that

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

[gpl]

The problem is that you have converted it to a string ... convert it back to a date and all will be well

convert(datetime, convert(varchar(10), date_time,101),101)

I have this handy function (which is region agnostic)

CREATE FUNCTION dbo.DateOnly(@ADate DateTime)
RETURNS[DateTime] AS
BEGIN
RETURN Convert(Datetime, datediff(d, 0, @ADate))
END


Graham

[anonymous]

The problem is that you have converted it to a string ... convert it back to a date and all will be well

convert(datetime, convert(varchar(10), date_time,101),101)

I have this handy function (which is region agnostic)

CREATE FUNCTION dbo.DateOnly(@ADate DateTime)
RETURNS[DateTime] AS
BEGIN
RETURN Convert(Datetime, datediff(d, 0, @ADate))
END


Graham

Thanks a Lot Mr.Graham..The query is working as required after converting back to date time..Ur help was very useful.