GROUP BY date

  • Thread starter Thread starter Grant
  • Start date Start date
G

Grant

I think I need to format the date in the table when I use GROUP by to group
the dates. The table has date and time stamp but I need it to group by the
date alone without the time stamp. How do I format the date field in the SQL
statement using SQL Server? I was able to do it in MS ACCESS but it is
different in SQL Server then MS ACCESS or .net then VB6.



The old way was "Select sum(tot) as total, dte from tblName group by
format(dte, 'mm/dd/yyyy') "




Thanks
 
In this instance, you could create a function in T-SQL like this...(this one
is DATETIME, but you could easily substitute SMALLDATETIME)
CREATE FUNCTION DateOnly_dt
(@DateAndTime AS DATETIME)
RETURNS DATETIME
AS
/*****************************************
* return date without time for datetime
*****************************************/
BEGIN
RETURN CAST(ROUND(CAST(@DateAndTime AS FLOAT),0,1) AS DATETIME)
END

Formatting isn't really your problem, it's making the date appear without
the time (which this is not doing exactly, it will just make all the times
Midnight so the group by clause will consider them together).

Then you could just use:

"Select sum(tot) as total, dte from tblName group by
dbo.DateOnly_DT(dte)"

And it should give you waht you want. Good Luck
 
Heh, I'll throw in my two cents. :-)

Dates are stored as floats by SQL-Server with the integer portion
representing days and the fractional portion representing milleseconds.

The most efficient way to group by day would be:

SELECT COUNT(*) from persons group by cast(MyDate AS INT)


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
Back
Top