Group By Date

I have an Employees Table, I want to retrieve the data using group by Date.
Select count(*),Joined_Date from Employees Group By Joined_Date.
The problem here is it groups by both date and time. I want to group by only
date.
I want the results for each and every day. Is there someother to do this
other than using groups.
Thanks
KiranHi,
Try something like this. It gives by dates and excludes the time
Select count(*),convert(varchar,Joined_Date,103
)
from Employees Group By convert(varchar,Joined_Date,103)
HTH..
Regards,
Dilip|||Kiran
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'2005-08-02 11:24:14.697')
INSERT INTO #Test VALUES (2,'2005-08-02 12:24:14.697')
INSERT INTO #Test VALUES (3,'2005-08-02 13:24:14.697')
INSERT INTO #Test VALUES (4,'2005-08-02 14:24:14.697')
INSERT INTO #Test VALUES (5,'2005-08-03 11:24:14.697')
INSERT INTO #Test VALUES (6,'2005-08-03 12:24:14.697')
INSERT INTO #Test VALUES (7,'2005-08-03 13:24:14.697')
INSERT INTO #Test VALUES (8,'2005-08-03 14:24:14.697')
SELECT COUNT(*) ,CONVERT(CHAR(10),col2,112) FROM #Test
GROUP BY CONVERT(CHAR(10),col2,112)
Note: Take a look at CONVERT system function that has a style (third)
parameter.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com...
>I have an Employees Table, I want to retrieve the data using group by Date.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group by
> only
> date.
> I want the results for each and every day. Is there someother to do this
> other than using groups.
> Thanks
> Kiran|||Thanks Uri
"Uri Dimant" wrote:

> Kiran
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 DATETIME NOT NULL
> )
> INSERT INTO #Test VALUES (1,'2005-08-02 11:24:14.697')
> INSERT INTO #Test VALUES (2,'2005-08-02 12:24:14.697')
> INSERT INTO #Test VALUES (3,'2005-08-02 13:24:14.697')
> INSERT INTO #Test VALUES (4,'2005-08-02 14:24:14.697')
> INSERT INTO #Test VALUES (5,'2005-08-03 11:24:14.697')
> INSERT INTO #Test VALUES (6,'2005-08-03 12:24:14.697')
> INSERT INTO #Test VALUES (7,'2005-08-03 13:24:14.697')
> INSERT INTO #Test VALUES (8,'2005-08-03 14:24:14.697')
>
> SELECT COUNT(*) ,CONVERT(CHAR(10),col2,112) FROM #Test
> GROUP BY CONVERT(CHAR(10),col2,112)
> Note: Take a look at CONVERT system function that has a style (third)
> parameter.
>
> "Kiran" <Kiran@.discussions.microsoft.com> wrote in message
> news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com...
>
>|||Thanks Dilip
"dilipn123@.gmail.com" wrote:

> Hi,
> Try something like this. It gives by dates and excludes the time
> Select count(*),convert(varchar,Joined_Date,103
)
> from Employees Group By convert(varchar,Joined_Date,103)
> HTH..
> Regards,
> Dilip
>|||You have to convert the date to a non-time consisting datetime, like
Select CONVERT(VARCHAR(10),GETDATE(),112)
Select count(*),CONVERT(VARCHAR(10),Joined_Date
,112) from Employees Group By
CONVERT(VARCHAR(10),Joined_Date,112).
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kiran" wrote:

> I have an Employees Table, I want to retrieve the data using group by Date
.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group by on
ly
> date.
> I want the results for each and every day. Is there someother to do this
> other than using groups.
> Thanks
> Kiran|||In news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com,
Kiran <Kiran@.discussions.microsoft.com> said:
> I have an Employees Table, I want to retrieve the data using group by
> Date.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group
> by only date.
Why does everyone convert the date to char?
Select count(*),Joined_Date
from Employees
Group By cast(cast(Joined_Date as float) as int)
Casting the datetime to an int doesn't truncate, it rounds, which is IMHO
very odd indeed.
Steve|||> Why does everyone convert the date to char?
Converting dates to a number works well but defies common-sense logic.
I don't know why MS allows this weird conversion between dates and
numerics. Note that they only guarantee the consistency of round-trip
conversions between versions.
Another method:
...
GROUP BY DATEDIFF(DAY,0,joined_date)
David Portas
SQL Server MVP
--|||In news:1122975714.884084.99980@.g44g2000cwa.googlegroups.com,
David Portas <REMOVE_BEFORE_REPLYING_dportas@.acm.org> said:
> Converting dates to a number works well but defies common-sense logic.
> I don't know why MS allows this weird conversion between dates and
> numerics.
It isn't weird when you consider how datetime values are stored. They are
simply a floating point number where the integer part is the number of days
since 1/1/1900 and the fractional part is the fraction through the day (so
.5 is midday). Many programming languages follow this convention, although
the date base is often different and shouldn't be relied upon. It allows
simple calculation of time passage (for example, how many days between x and
y? Answer = y-x).
Steve|||> It isn't weird when you consider how datetime values are stored
Yes of course, but in SQL the datatypes are supposed to insulate us
from the way data is stored. MS might well choose to change the storage
architecture in a future version but that shouldn't change the logical
meaning of our code. That's why I'm uncomfortable with date/numeric
conversions, although I admit that I too find them convenient
sometimes.

> It allows
> simple calculation of time passage (for example, how many days between x a
nd
> y? Answer = y-x).
I would always recommend you use the proper date arithmetic functions
for this rather than rely on the numeric math operators. There's a
world of difference between y-x and DATEDIFF(DAY,x,y). For example:
DECLARE @.dt1 SMALLDATETIME, @.dt2 SMALLDATETIME
SET @.dt1 = '20050101'
SET @.dt2 = '19000101'
SELECT DATEDIFF(DAY,@.dt1,@.dt2)
SELECT @.dt2 - @.dt1
David Portas
SQL Server MVP
--