Convert(varchar(8), datefield, 1) Changes the internal representation of datetime data to: mm/dd/yy format (8 characters) which can be dangerous for data that spans centuries. ;)
You were thinking of the ODBC string representation of datetime data which is not applicable in this case since the execution plan is compiled and executed on MS SQL Server side (and not at the ODBC layer). ---------------------------- James Ang Senior Programmer MedSeek, Inc. [EMAIL PROTECTED] -----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 4:39 PM To: CF-Talk Subject: RE: SQL Question: Selecting and grouping by date > 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 The numbers may be a little off, but that's not a bad idea, and probably significantly faster than converting dateparts to strings and then back to a date again... afaik, the standard ODBC date format is "ts { YYYY/MM/DD timeinfo }" so maybe CONVERT(smalldatetime,SUBSTRING(CONVERT(varchar,datetimefield),6,10)) .. or something to that effect... actually I'm not sure if I have the parameters for the SUBSTRING() function in the right order... and I'm not sure how much of a concern the possibility of the format of that date string changing might be ... Isaac Dealey Certified Advanced ColdFusion Developer www.turnkey.to 954-776-0046 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
