| Author |
Topic |
|
benjamintb
Starting Member
15 Posts |
 Posted - 2004-05-20 : 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
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-05-20 : 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
15 Posts |
Posted - 2004-05-20 : 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_AbbrevFROM 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_IdWHERE (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_AbbrevI 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
1456 Posts |
Posted - 2004-05-20 : 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
15 Posts |
Posted - 2004-06-10 : 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
3271 Posts |
Posted - 2004-06-11 : 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
1961 Posts |
Posted - 2004-06-11 : 16:26:25
|
quote:
Originally posted by LumbagoI 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
3271 Posts |
Posted - 2004-06-12 : 04:10:00
|
| Is it really slower...? Haven't tested to be honest but I'll take your word for it |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-12 : 06:22:07
|
| [code]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[/code][fx:Toddles off to do Global Find&Replace] :-(Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-06-12 : 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
1961 Posts |
Posted - 2004-06-12 : 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. |
|
|
|
|
date grouping