469,579 Members | 1,220 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

How to group by date irrespective of time

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..
Mar 6 '08 #1
12 18446
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT trunc(dat), COUNT(*) FROM table1 GROUP BY trunc(dat)
  3.  
  4.  
Mar 6 '08 #2
This query works in Oracle, but i want it in MS SQL.
Mar 6 '08 #3
ck9663
2,878 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. select convert(varchar(10), YourDateColumn,101), count(*)
  2. from YourTable 
  3. group by convert(varchar(10), YourDateColumn,101)
  4.  
-- CK
Mar 6 '08 #4
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..
Mar 6 '08 #5
ck9663
2,878 Expert 2GB
use ordinal position of the columns

Expand|Select|Wrap|Line Numbers
  1. ORDER BY 1 

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

-- CK
Mar 6 '08 #6
use ordinal position of the columns

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 6 '08 #7
ck9663
2,878 Expert 2GB
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
Mar 6 '08 #8
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?
Mar 6 '08 #9
ck9663
2,878 Expert 2GB
Sorry, I missed the year part. Since you converted the date to varchar, it sort it like a string. Try a:

Expand|Select|Wrap|Line Numbers
  1. ORDER BY DATE_TIME
instead. I believe it will sort it even if it's not on your SELECT list.

-- CK
Mar 6 '08 #10
Sorry, I missed the year part. Since you converted the date to varchar, it sort it like a string. Try a:

Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 6 '08 #11
gpl
152 100+
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
Mar 6 '08 #12
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.
Mar 6 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by priyasmita_guha | last post: by
7 posts views Thread by matteosartori | last post: by
9 posts views Thread by insomniux | last post: by
2 posts views Thread by kirke | last post: by
2 posts views Thread by Stevienashaa | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.