converting date/time to just date?

I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).

What I want is to do the following:

Say you have these records:

person | date-time
---+--------
jim | 06/02/2004 00:05:52
jim | 06/02/2004 05:06:21
jim | 06/02/2004 05:46:21
jim | 06/15/2004 11:26:21
jim | 06/15/2004 11:35:21
dave | 06/04/2004 09:35:21
dave | 06/04/2004 11:05:21
dave | 06/06/2004 10:34:21
dave | 06/08/2004 11:37:21

I'd like the results to count how many days and return

person | days
---+---
jim | 2
dave | 3

How would I do this?

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@.12jabber.com <--jabber IM ]On Tue, 22 Jun 2004 15:27:52 -0000, Sugapablo wrote:

>I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).
>What I want is to do the following:
>Say you have these records:
>person | date-time
>---+--------
>jim | 06/02/2004 00:05:52
>jim | 06/02/2004 05:06:21
>jim | 06/02/2004 05:46:21
>jim | 06/15/2004 11:26:21
>jim | 06/15/2004 11:35:21
>dave | 06/04/2004 09:35:21
>dave | 06/04/2004 11:05:21
>dave | 06/06/2004 10:34:21
>dave | 06/08/2004 11:37:21
>I'd like the results to count how many days and return
>person | days
>---+---
>jim | 2
>dave | 3
>How would I do this?

Hi Sugapablo,

SELECT person,
COUNT(DISTINCT CONVERT(CHAR(8), date-time, 114)) AS days
FROM YourTable
GROUP BY person
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql