| Author |
 Topic  |
|
|
benjamintb
Starting Member
Wallis and Futuna Islands
15 Posts |
 Posted - 05/20/2004 : 01:07:14
|
Hi,
I have a table that has a datetime datatype field. I want to group some records by the date but instead it groups them by both the date and the time. ie it would only group 2 records together if they were on the same date at the exact same time. I know datepart can get just the month or the year, etc but I need a function that pulls just the date without the time. I can`t change the fromat of the field to be just the date either as i need the time for other things. Any ideas???
Thanks in advance! |
|
|
timmy
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 05/20/2004 : 02:11:11
|
I find the easiest way to group on dates is to cast/convert it to an integer. Dates are held as decimals, being the number of days since a set date (I think it's 1-1-1900). So, your group by clause would be:
GROUP BY Convert(int, [dateCol])
Tim |
 |
|
|
benjamintb
Starting Member
Wallis and Futuna Islands
15 Posts |
Posted - 05/20/2004 : 02:42:40
|
Thanks for your reply! I tried it and it works perfect. The problem tho is that when i try the sql statement:
SELECT SUM(dbo.Call_Action.Billable_Time) AS Expr1, SUM(dbo.Call_Action.Non_Billable_Time) AS Expr2, CONVERT(int, dbo.Call_Action.Action_Dt),
dbo.Contact.F_Name + ' ' + dbo.Contact.L_Name AS Full_Name, dbo.Company.Company_Abbrev
FROM dbo.Call_Action INNER JOIN
dbo.Call ON dbo.Call_Action.Call_Id = dbo.Call.Call_Id INNER JOIN
dbo.Contact ON dbo.Call_Action.Staff_Id = dbo.Contact.Contact_Id INNER JOIN
dbo.Project ON dbo.Call.Project_Id = dbo.Project.Project_Id INNER JOIN
dbo.Company ON dbo.Project.Company_Id = dbo.Company.Company_Id
WHERE (dbo.Call_Action.Action_Dt > GETDATE() - 2)
GROUP BY CONVERT(int, dbo.Call_Action.Action_Dt), dbo.Contact.F_Name + ' ' + dbo.Contact.L_Name, dbo.Company.Company_Abbrev
I can only get the date in the form of a number. I need to use the actual date in sql statement for a pie chart. Is there any way that you can group the date while keeping it displayed as a date instead of a number? I`m using crystal reports so I don`t think there is a function to convert the number back to a date that i can use...
Any ideas much appreciated! |
|
|
|
mohdowais
Sheikh of Yak Knowledge
United Arab Emirates
1456 Posts |
Posted - 05/20/2004 : 03:33:55
|
Stripping out the time element from a datetime value. Done enough times here.
SELECT dateadd(d, datediff(d, 0, getdate()), 0)
OS |
|
|
|
benjamintb
Starting Member
Wallis and Futuna Islands
15 Posts |
Posted - 06/10/2004 : 22:45:57
|
| Thanks for that. I`ve got it all figured out now. Its pretty simple once you know the syntax :) |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 06/11/2004 : 13:40:12
|
I'm sure mohdowais method works just fine but I think a simple convert of the datetime to a date-only value is alot smoother:
SELECT CONVERT(varchar(8), DateFied, 101)
...
GROUP BY CONVERT(varchar(8), DateFied, 101)
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 06/11/2004 : 16:26:25
|
quote:
Originally posted by Lumbago
I think a simple convert of the datetime to a date-only value is alot smoother:
Not sure about smoother, but it's certainly slower.
|
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 06/12/2004 : 04:10:00
|
| Is it really slower...? Haven't tested to be honest but I'll take your word for it |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 06/12/2004 : 06:22:07
|
Seconds Test
------- ----------------------------
1.453 SELECT COUNT(*) FROM MyTable
(Result = 12,689,651 rows)
6.813 SELECT @TempDate = MyDate FROM MyTable
6.623 SELECT @TempDate = DATEADD(day, DATEDIFF(day, 0, MyDate), 0) FROM MyTable
37.203 SELECT @TempDate = CONVERT(varchar(8), MyDate, 101) FROM MyTable
[fx:Toddles off to do Global Find&Replace] :-(
Kristen |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 06/12/2004 : 08:45:23
|
Damn! Sorry for swearing but when I said that my method was smoother than mohdowais I actually meant timmys! Didn't even notice that weird but yet smooth solution that mohdowais actually had...
--
Lumbago
(that will also toddle off to do a global search & replace  ) |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 06/12/2004 : 09:15:01
|
Oh, I didn't even consider Timmy's method, because it isn't usually what you want.
Specifically, CONVERT(int, [datecol]) transitions at noon, not midnight.
|
|
|
| |
Topic |
|
date grouping
