Thanks Jay,

That worked!


Craig

-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x12406475.[Email address protected]
Sent: Thursday, March 30, 2006 1:02 PM
To: LazyDBA Discussion
Subject: RE: compare date to datetime

The simplest way is to strip the date from the datetime column like this:
....
WHERE Convert( varchar, date_enrolled, 112 ) = @current_admit_date

But, that will not use an idex on the column (if there is one). So, the next
best thing is to compare the column to a range of dates like this:
WHERE date_enrolled BETWEEN @current_admit_date AND DateAdd( dd, 1,
@current_admit_date )

You actually want to subtract 3 milliseconds from the end datetime to ensure
that it is 00:00:00.000 through 23:59:59.997 (the last possible timepoint in
a day). Otherwise, you will pick up rows from the following day at midnight.
I was just a bit too lazy to figure out how many milliseconds fall between
those times...

Jay




From: Comperatore Craig
Sent: Thu 30-Mar-06 12:50
To: LazyDBA Discussion
Subject: compare date to datetime


Hello gurus -



I am writing a stored procedure where I need to compare a date to a datetime
field.



For example, part of my WHERE clause is:

WHERE date_enrolled = @current_admit_date



(date_enrolled is the system stored datetime field, and @current_admit_date
is my variable, which will be input as mm/dd/yyyy)



For my purposes, 11/03/2005 is equal to 2005-11-03 17:00:00.000, or any
other time on that day. I just need to lop off the time. I imagine I would
have to use CAST or CONVERT on date_enrolled, but I can not seem to make
this work.



Thanks,



Craig Comperatore, CNE, MCP, CCNA, A+, Net+

IT Site Coordinator, Baycare - the Harbor BHCI

Phone: (727) 841-4200 x 248

Fax: (727) 841-4203



Confidential: This electronic message and all contents contain information
from BayCare Health System which may be privileged, confidential or
otherwise
protected from disclosure. The information is intended to be for the
addressee
only. If you are not the addressee, any disclosure, copy, distribution or
use
of the contents of this message is prohibited. If you have received this
electronic message in error, please notify the sender and destroy the
original
message and all copies.


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidential: This electronic message and all contents contain information
from BayCare Health System which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the addressee
only. If you are not the addressee, any disclosure, copy, distribution or use
of the contents of this message is prohibited. If you have received this
electronic message in error, please notify the sender and destroy the original
message and all copies.

MS Sql Server LazyDBA home page