473,581 Members | 2,734 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to group by date irrespective of time

98 New Member
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 18889
amitpatel66
2,367 Recognized Expert Top Contributor
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
anonymous
98 New Member
This query works in Oracle, but i want it in MS SQL.
Mar 6 '08 #3
ck9663
2,878 Recognized Expert Specialist
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
anonymous
98 New Member
This query is working fine.
I want to order the result based on dates..so i added
order by convert(varchar (10), Transaction_dat e_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..Plea se help me in solving this..
Mar 6 '08 #5
ck9663
2,878 Recognized Expert Specialist
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
anonymous
98 New Member
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 Recognized Expert Specialist
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
anonymous
98 New Member
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 Recognized Expert Specialist
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

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

Similar topics

2
1773
by: Gerry Abbott | last post by:
Hi all, Is there a convenient way to trap the date/time of the last change to the data (in the tables) in a database. (the date modified changes when the file is accessed, irrespective of changes, so that's of little value) Thanks,
13
2197
by: priyasmita_guha | last post by:
Here is a program- /* PROGRAM: To find the difference between two dates */ #include<dos.h> #include<stdio.h> #include<conio.h> #include<process.h> void valid_date(int,int,int); int getkey(void); void differ_dat(struct date *,struct date *); const int mth={31,28,31,30,31,30,31,31,30,31,30,31};
7
1694
by: matteosartori | last post by:
Hi all, I've spent all morning trying to work this one out: I've got the following string: <td>04/01/2006</td><td>Wednesday</td><td>&nbsp;</td><td>09:14</td><td>12:44</td><td>12:50</td><td>17:58</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>08:14</td> from which I'm attempting to extract the date, and the five...
9
6363
by: insomniux | last post by:
Hi, I am having a problem with formatting the default value of a date field. It has been discussed earlier in many topics, but still I cannot solve the problem. What's happening: I have various forms which are based on an ODBC-linked tables. In one of the forms, I have a control which shows the date of a date field in my database (storage...
2
11991
by: kirke | last post by:
Hi, I have a datetime column named dtDateTime. its format is "Oct 27 2006 12:00:00 " I want to group by only date part of it and count my code is $sql1="SELECT convert(varchar,J1708Data.dtDateTime,120), count(convert(varchar,J1708Data.dtDateTime,120))
0
1024
by: mykhan | last post by:
I have been using access database with asp.net pages. I just realized a problem with date saving in access table. I didnt give any specific format for the date when saved in tables, because I wanted to control the date formats from another page, what ever type selected, the date should be displayed in that format, US, UK, short date etc etc. ...
13
29337
by: Killer42 | last post by:
Hi all. Hopefully a simple one for any SQL guru. In an Access query, how can I group by a date field, without having my data broken down by time? In other words, I just want a count per day, not per second or whatever. I'm using Access 2003, but I doubt it matters in this case. At present I am just using a function (see below) to strip...
2
2356
by: Stevienashaa | last post by:
Hello I'm using Access 2003, and I have a query (written in SQL) which has two parameters and asks the user for two dates. This has been working fine. Today I modified the query, removing the paramerters and hard- coding some dates in, in dd/mm/yy format. To my surprise I got a different number of records returned. I then, as an experiment,...
1
1908
by: mcgr0199 | last post by:
How do I separate a date/time stamp in Access 2007? I need to be able to query for certain times of day over a period of 120 days (i.e. create a query that only gives me the data for 1pm, irrespective of the date). Any suggestions?
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8132
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8296
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7878
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5665
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3806
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1118
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.