SQL to convert eAgentLoginStat.Timestamp to Date only

[rmscho02]

I'm querying the eAgentLoginStat table and need help with the SQL. I want to get the shift duration for each agent per day, so I need to do a group by Timestamp and AgentID in the query. the problem is the timestamp doesn't group by day because it has the time in it. How can I convert the timestamp to just the date within the SQL? Do I use something like CAST or CONVERT (neither of which I'm sure how to use).

Thanks!

 

[Utreg]

The eAgentLoginStat table logs all events with timestamp, for this I use the dAgentPerformanceStat.LoggedInTime table.

I use the following formula (in Crystal Reports) to convert to a date: Date({dAgentPerformanceStat.Timestamp})

For calculating a shift duration I also use the formulas:
FirstLoginMIN = Minimum({eAgentLoginStat.Time},{eAgentLoginStat.UserID})
LastLogoutMAX = Maximum({eAgentLoginStat.Time},{eAgentLoginStat.UserID})

Here a copy of the SQL query, hope this helps;
SELECT eAgentLoginStat.Timestamp, eAgentLoginStat.UserID, eAgentLoginStat.Time
FROM blue.dbo.eAgentLoginStat eAgentLoginStat
WHERE eAgentLoginStat.UserID=0x

 

[darkscion0]

If you just need LoggedInTime have you thought about querying the dAgentPerformanceStat as there is a LoggedInTime column in that table. The benefit of using the dAgentPerformanceStat table is that shifts that last past midnight are broken into the two different days. So you don't end up with a LoggedIn and no LoggedOut event for the day.

 

[rmscho02]

I need the entire shift duration, which from what I can tell is more than just the LoggedInTime.