SQL: Count based on Date on a date/time field type

[LookitsPuck]

Hey all!

I'm a newbie SQL'er. I know the basics and whatnot, however, I have an issue. I have a database in which I'm trying to count the number of times a day an event happens. However, the field that I'm grouping by has a date/time field. If I count on that field and group by that field, every item will have a count of one since it's counting based upon date and time instead of date. I'd be hard pressed to find an event that occurred at the same day at the same time (example, 2 events occuring at 5/07/04 at 4:00PM). Now say 7 events occurred on 5/07/04, I want to be able to count those. Is there a way just to count based upon the grouping of JUST the date portion of the date/time variable?

Here's the SQL statement I have currently:

SELECT Count(Initial.planned_start) AS JustDate
FROM Initial
WHERE (((Initial.planned_start)<>False)) GROUP BY Initial.planned_start;


planned_start is a date/time field. How would I have it group by planned_start's date portion instead of both date and time?

Thanks,
-Steve

[Gabriel Fleseriu]

Hi Steve.

I don't know what database you use, so I will write the statement as if you used ORACLE.


select count(D) from (
select trunc(your_date) D from your_table
where your_date is not null
)
group by D;


Basically, you nest two selects...

[LookitsPuck]

I'm using SQL Server. What's the equivalent of your query in SQL Server? I have a query setup that breaks the date/time value into 3 different values, however, I can't add them together (to make the full date) because it treats them as integer values. :( Here's the code:

SELECT DatePart("d",Initial.planned_start) + DATEPART
("m", Initial.planned_start) + DATEPART("yyyy",
Initial.planned_start) AS FullDate
FROM Initial;


What's your code translated for SQL Server?

BTW, I'm in Microsoft Access ODBC'd to our SQL Server database.

[LookitsPuck]

I cheated and converted it so some sort of date format by then querying the YearMonthDay query:


SELECT ((Month*1000000)+(Day*10000)+(Year)) As FullDate
FROM YearMonthDay;


That gives 231983 for February 3, 1983 2221983 for February 22, 1983, and so on and so forth. I guess I'll just have to group by that instead of writing some code to convert the number into date/time format.

Is there a function right out of a box that lobs off the time portion of a date/time variable so its just a date value?

The problem with comparing those integer date results is that 1121983 could either mean November 2, 1983 or January 12, 1983. :-/ I could change it so it has the Month multiplied by an extra zero, therefore its not possible for semantical problems. However, this is not an intuitive value, that's why I'm looking to see how just to truncate the time off the date/time value.

-Steve

[hspc]

Is there a function right out of a box that lobs off the time portion of a date/time variable so its just a date value?
try :

CAST(CONVERT(char(12), GETDATE(), 3) AS smalldatetime)