Wait ... one more, please ... haha An open item on my to-do list is start learning more about SQL Server, so this stuff helps. In Oracle it is sooo easy ... trunc(datefield).
Here is what I found in SQL Server, since 'datevalue()' doesn't exist! ... don't know if is any better or not ... select cast(floor(cast(datefield as float)) as datetime) as mydate, sum(numberfield) as mysum from table group by cast(floor(cast(datefield as float)) as datetime) It converts the date to a number, takes the integer of that (midnight), then converts it back to a date. HTH, Dan -----Original Message----- From: Brook Davies [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 4:52 PM To: CF-Talk Subject: RE: SQL Question: Selecting and grouping by date Thank you all so much for the help! You guys are great. I tried all of the examples. I learned a fair bit during the process. I ended up using the example below which worked great. Thanks again, you've all done your good deed for the day ;) Now go home to your wives! At 04:25 PM 30/07/02 -0700, you wrote: >Argh. I forgot that the GROUP BY clause does not like aliases. :( > >SELECT Year(datetimefield) as YY, Month(datetimefield) as MM, >Day(datetimefield) as DD, > Count(1) AS RecordCountForThatDay >FROM yourtable >GROUP BY Year(datetimefield), Month(datetimefield), >Day(datetimefield) >ORDER BY YY, MM, DD > >I tend to stay away from Convert() for this problem, but if you prefer >the string manipulation route, you should look into: > >Select convert(varchar(8), datetimefield, 1) as adatefield > From yourtable >Order by adatefield > >---------------------------- >James Ang >Senior Programmer >MedSeek, Inc. >[EMAIL PROTECTED] > > > >-----Original Message----- >From: James Ang >Sent: Tuesday, July 30, 2002 4:14 PM >To: CF-Talk >Subject: RE: SQL Question: Selecting and grouping by date > > >Month(datetimefield) instead of Month() > >Buggy example, but you get the idea. ;) > >---------------------------- >James Ang >Senior Programmer >MedSeek, Inc. >[EMAIL PROTECTED] > > >-----Original Message----- >From: James Ang >Sent: Tuesday, July 30, 2002 4:13 PM >To: '[EMAIL PROTECTED]' >Subject: RE: SQL Question: Selecting and grouping by date > > >If you don't need to perform aggregation: > >SELECT Year(datetimefield) as YY, Month() as MM, Day(datetimefield) >as DD, * >FROM yourtable >ORDER BY YY, MM, DD > >If you need to perform aggregation based on date (and not date AND >time): >SELECT Year(datetimefield) as YY, Month() as MM, Day(datetimefield) >as DD, > Count(1) AS RecordCountForThatDay >FROM yourtable >GROUP BY YY, MM, DD >ORDER BY YY, MM, DD > >Hope this helps. > >---------------------------- >James Ang >Senior Programmer >MedSeek, Inc. >[EMAIL PROTECTED] > > > >-----Original Message----- >From: Brook Davies [mailto:[EMAIL PROTECTED]] >Sent: Tuesday, July 30, 2002 4:24 PM >To: CF-Talk >Subject: RE: SQL Question: Selecting and grouping by date > > >Thanks for the quick help, I tried the datepart function but it doesn't >look like it can return more the the date, month OR year. I need to get >all >parts or there may be some inconsistencies in the data. > >I also tried the Ben's suggestion using Concatenation. > >SELECT Month(myDate) + '/' + Day(myDate) + '/' + Year(myDate) > >But I get an error : Syntax error converting the varchar value '/' to a >column of data type int. > > >At 04:01 PM 30/07/02 -0700, you wrote: > >Have you tried using the DATEPART() Transact-SQL function? > > > >SELECT ID, FirstName, LastName, DateCreated > >FROM Contact > >WHERE DATEPART("m",DateCreated) = 7 > > > >The above query will return all Contacts create in the month of July > >(for any year). > > > >Here are the details for the DATEPART() function: > >http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlre >f > >/ts_da-db_2mic.asp > > > >Hope this helps :) > > > >---- > >SCOTT VAN VLIET > >BRD.WRKS INTERACTIVE > >T: 714.469.6805 > >E: [EMAIL PROTECTED] > > > > > >-----Original Message----- > >From: Brook Davies [mailto:[EMAIL PROTECTED]] > >Sent: Tuesday, July 30, 2002 4:05 PM > >To: CF-Talk > >Subject: SQL Question: Selecting and grouping by date > > > >Is there a way to select the datepart(mydate,"mm/dd/yy") from MSSQL? I > >can > >only seem to return the Day, month or year, but not all three. The >field > > > >contains date & time data and I am trying to group on the date part >but > > > >the time is throwing off the results. > > > >Brook > > > > > > > > > > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
