In Access datevalue(datefield) does what you are trying to do -- truncate the date. This will allow you to group by the date field. I imagine it is the same in SQL Server.
Dan -----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/tsqlref >/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 > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
