Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 22nd, 2006, 04:28 PM
Authorized User
Join Date: Mar 2004
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Querying against truncated dates

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
Old September 22nd, 2006, 04:47 PM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

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.
Old September 23rd, 2006, 05:25 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

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.:
SELECT CAST(CONVERT(varchar, yourdatetime, 112) AS datetime) ...
Jeff Mason
Custom Apps, Inc.
Old October 16th, 2006, 11:55 PM
Friend of Wrox
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts

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

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

--Jeff Moden

Similar Threads
Thread Thread Starter Forum Replies Last Post
Truncated field when copying Tachyophan Access VBA 3 November 16th, 2006 08:23 AM
Long Name truncated with SendMail echovue Excel VBA 0 May 12th, 2005 03:05 PM
Truncated Form Values. conscience Pro PHP 1 December 27th, 2004 02:53 PM
need to be truncated PatrickH SQL Server ASP 2 April 15th, 2004 10:48 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.