Datetime handling in SQL

[Matt Graham]

I have a table with a datetime field. This is populated in a real-time
production environment with the getdate() function. I need to produce some
reports with statistics reported by day. When I use the SQL

SELECT DISTINCT event_datetime, count(a_events), count(b_events) GROUP BY
event_datetime ORDER BY every_datetime

I don't get date groupings, obviously, but rater every records because the
time varies slightly. I tried...
SELECT DISTINCT CAST(event_datetime AS char(11)), count(a_events),
count(b_events) GROUP BY CAST(event_datetime AS char(11)) ORDER BY
CAST(event_datetime AS char(11))
but it runs very slow against 700,000 records and I loose the ability to
enhance the query with date functions to narrow the scope.

What is the best way to handle this?

How can I insert just the date in the table using the database servers
date/time? I can't use client side timestamps because of the need to capture
absolute production sequence (incorrect client clocks would reports bad
information).

Any QUICK help would be appreciated.

[BP Margolin]

Matt,

How about adding a column to the table that automatically "truncates" the
time portion of the date/time ... you can do this via an INSERT trigger ...
something like:

create table Matt
(
MattPK int NOT NULL primary key,
c1 datetime NOT NULL,
c2 datetime NULL
)
go

create trigger i_Matt on Matt for insert
as
update Matt
set c2 = cast(convert(char(8), c1, 112) as datetime)
where exists (select *
from INSERTED as i
where i.MattPK = MattPK)
go

insert into Matt (MattPK, c1) values (1, getdate( ))
select * from Matt

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Matt Graham" <m.j.g...@home.com> wrote in message
news:xN827.7206$Y6.25...@news1.rdc2.pa.home.com...

[Erland Sommarskog]

Matt Graham (m.j.g...@home.com) writes:
> SELECT DISTINCT event_datetime, count(a_events), count(b_events) GROUP BY
> event_datetime ORDER BY every_datetime
>
> I don't get date groupings, obviously, but rater every records because the
> time varies slightly. I tried...
> SELECT DISTINCT CAST(event_datetime AS char(11)), count(a_events),
> count(b_events) GROUP BY CAST(event_datetime AS char(11)) ORDER BY
> CAST(event_datetime AS char(11))
> but it runs very slow against 700,000 records and I loose the ability to
> enhance the query with date functions to narrow the scope.

I fail to see why you cannot narrow the scope. You can still put in
a WHERE clause restricting the output to a certain date interval.

As for performance, a non-clustered index on (event_datetime, a_events,
b_events) would be recommendable. If you combine it with the extra column
that BP suggested, that is with index on (event_date, a_events, b_events),
the results would be even better.

--
Erland Sommarskog, Abaris AB
som...@algonet.se
SQL Server MVP

[Robert Lummert]

Hi Matt,
the use of both, distinct and group by statements, makes no real sense.
Anyway, something like

SELECT
datepart(dd,event_datetime),
datepart(mm,event_datetime),
datepart(yyyy,event_datetime),
count(a_events),
count(b_events)
GROUP BY
datepart(dd,event_datetime),
datepart(mm,event_datetime),
datepart(yyyy,event_datetime)
ORDER BY
datepart(dd,event_datetime),
datepart(mm,event_datetime),
datepart(yyyy,event_datetime)

should work. Have fun

Robert

[Robert Lummert]

Hi Matt,
the use of both, distinct and group by statements, makes no real sense.
Anyway, something like

SELECT
convert(char(10),getdate(),101),
count(a_events),
count(b_events)
GROUP BY
convert(char(10),getdate(),101)
ORDER BY
convert(char(10),getdate(),101)

[TRaef06]

Try this:

convert(char(10), getdate(), 102)

This will return:

2001.07.09 as of today anyway.

This use of convert is usually needed when comparing dates without comparing
the time part of the datetime type.

Hope this helps soon enough!

Thomas J. Raef
Freelancer's Journal, Inc.