converting date/time to just date?

[Sugapablo]

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 ]
[ su*******@12jabber.com <--jabber IM ]

[Hugo Kornelis]

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)