December 14, 2007 at 4:56 am
Hi,
I've got a table that holds tasks updated by user simplified to three columns only:
TaskID UserName ModifiedDate
int varchar smalldatetime
0 admin 14/12/2007 01:34:00
1 admin 14/12/2007 02:23:00
3 user 13/12/2007 03:23:00
4 user 13/12/2007 05:53:00
5 user 14/12/2007 06:53:00
6 user 14/12/2007 07:53:00
7 user 14/12/2007 08:53:00
etc.
now I need to create a stored proc (with function to return New table) with In params:
@UserName
@StartDate
@EndDate
And I want to return a table for UserName "user":
DayDate NoOfUpdatedTasks
13/12/2007 2
14/12/2007 2
My Problems:
1) i cannot convert date from long format 13/12/2007 05:53:00 to 13/12/2007 day only (I'm using set @StartDate = convert (datetime, @StartDate, 103)
2) when I count distinct it always returns full date and number of tasks 1
Please Help!
kris
December 14, 2007 at 5:22 am
Use something along these lines (no guarantee, since you didn't post table structure - but it should give you idea how to strip time from datetime value):
SELECT DATEADD(d, DATEDIFF(d, 0, ModifiedDate), 0) as day_only,
COUNT(*) as No_of_updated_tasks
FROM tasktable
WHERE user = @user
GROUP BY DATEADD(d, DATEDIFF(d, 0, ModifiedDate), 0)
ORDER BY 1
December 14, 2007 at 6:23 am
Exactly what I wanted!
Thank you Vladan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply