I need to group by date in some stored procedures, unfortunately mydata is all date time format yyyymmdd hh:mm:SS:msmsms

Can someone tell me how to truncate the long datetime to just a date in a query or store procedue. There doesnot appear to be time related function for this. I am trying to calculate the number. I even tried to write a udf, but I couldn't get the date time function support to take the hours and min to zero. Even tried using the round function, but the datatype kept it from working...

Please Help

John Pennington


John Pennington
 
United Parcel Service
w(404) 828 6934
c (770) 714 5975

__________________
John Pennington
 
United Parcel Service
w(404) 828 6934
c (770) 714 5975

How about this?

Convert(varchar(10), [datatimefield], 101) that will give you MM/DD/YYYY

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

Then you can CAST that back to a datetime, and the time portion has been removed, or more properly, set to midnight on the date in question, i.e.:
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

An alternate method without tripping through the performance-challenged world of character conversions is...

SELECT DATEADD(dd,DATEDIFF(dd,0,yourdatetime),0) ...

--Jeff Moden