SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 date grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

benjamintb
Starting Member

Wallis and Futuna Islands
15 Posts

Posted - 05/20/2004 :  01:07:14  Show Profile  Reply with Quote
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  Show Profile  Visit timmy's Homepage  Reply with Quote

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
Go to Top of Page

benjamintb
Starting Member

Wallis and Futuna Islands
15 Posts

Posted - 05/20/2004 :  02:42:40  Show Profile  Reply with Quote
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!
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 05/20/2004 :  03:33:55  Show Profile  Visit mohdowais's Homepage  Reply with Quote
Stripping out the time element from a datetime value. Done enough times here.

SELECT dateadd(d, datediff(d, 0, getdate()), 0)

OS
Go to Top of Page

benjamintb
Starting Member

Wallis and Futuna Islands
15 Posts

Posted - 06/10/2004 :  22:45:57  Show Profile  Reply with Quote
Thanks for that. I`ve got it all figured out now. Its pretty simple once you know the syntax :)
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/11/2004 :  13:40:12  Show Profile  Reply with Quote
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"
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 06/11/2004 :  16:26:25  Show Profile  Reply with Quote
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.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/12/2004 :  04:10:00  Show Profile  Reply with Quote
Is it really slower...? Haven't tested to be honest but I'll take your word for it
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/12/2004 :  06:22:07  Show Profile  Reply with Quote

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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/12/2004 :  08:45:23  Show Profile  Reply with Quote
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 )
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 06/12/2004 :  09:15:01  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.38 seconds. Powered By: Snitz Forums 2000