Counting a Datetime field

[mGis]

A simple one for the Guru's:

tblUsers
Usr_CreatedOn  =  Datetime Field

I need to count when users singed up by date, but the field is a datetime so the following won't work:

SELECT Usr_CreatedOn as Reg_Date, COUNT(Usr_CreatedON) as Total
FROM tblUsers
GROUP BY USr_CreatedON;

it counts the time interval, so each user is counted seperately

Looking for:

Reg_Date        Total
03/01/2001         34
03/02/2001         43
Ect...

have tried CONVERT, and TRIM plus others

thanks for the help,
Michael

 

[THoey]

Try this:

SELECT SUBSTR(Usr_CreatedOn, 1, 10) as Reg_Date, COUNT(Usr_CreatedON) as Total
FROM tblUsers
GROUP BY SUBSTR(Usr_CreatedOn, 1, 10);

Or:

SELECT TRUNC(Usr_CreatedOn) as Reg_Date, COUNT(Usr_CreatedON) as Total
FROM tblUsers
GROUP BY TRUNC(Usr_CreatedOn);

Either should work, if memory serves me correctly...

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[mGis]

I forgot to mention that i'm working in SQL Server 7.0
which doesn't like:

'TRUNC' and
'SUBSTR' - ERROR- Statements could not be prepared 'TRUNC' or 'SUBSTR' is not recognized as a Function Name

'SUBSTRING' - ERROR- the DataType is invalid for the SubString Function, Allowed types are: char/varchar, nchar/nvarchar, binary, varbinary.

 

[Cagliostro]

insted of substring you can use
charindex(ColName,'WhatDoYouWantToCheck') John Fill

 

[mGis]

Tried charindex, but recieved same error

'CHARINDEX' - ERROR- the DataType is invalid for the CHARINDEX Function, Allowed types are: char/varchar, nchar/nvarchar, binary, varbinary.

 

[Cagliostro]

in "group by" you should use "Reg_Date" instead of "Usr_CreatedOn".
Also you may
charindex(cast(Usr_CreatedOn as varchar),whatdoyouwant) John Fill

 

[MalcolmW]

To convert datetime to date only string, try
CAST(Usr_CreatedOn AS CHAR(10)).
This resulting expression can be also then be CAST as a DATETIME or SMALLDATETIME, so that your result is expressed as a date type, if that is desirable for sorting purposes. The time values will be populated with zeros. Malcolm

 

[mGis]

Here is what I came up with:

select cast(Usr_CreatedOn as char(10)) as Reg_Date,
      count(Usr_CreatedOn)as Total
from tblusers
group by 
       cast(Usr_CreatedOn as char(10));

But, I am always looking for ways to improve my coding,
so any other suggestions are welcome

THANKS for your help,
Michael