Don''t include the time of datetime??

Question

Post reply

Don''t include the time of datetime??

Paula-196779

SSCrazy

Points: 2575
More actions
May 11, 2005 at 9:10 am

#67691

Hi all,
I have the following query which brings back all cases equal to a certain date and time. I want the query to bring back all cases on that date but at any time. I've tried using 'like' without the time part but can't seem to get the right results. Any ideas?
SELECT DCApr.CaseFullRef, DCCntHst.ContDesc, DCCntHst.SentDate
FROM DCACTNSL INNER JOIN DCCntHst ON DCACTNSL.LookupKey = DCCntHst.[Action] INNER JOIN DCApr ON DCCntHst.SystemKey = DCApr.SystemKey
WHERE (DCCntHst.SentDate < '2004-02-12') AND (DCACTNSL.Code LIKE '%Doc%') AND (DCCntHst.SentDate = CONVERT(DATETIME, '2003-10-24 00:00:00', 102))
ORDER BY DCCntHst.SentDate
(The problem area is in red)
Thanks,
Paula

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 1

Try using something like between. Here's something that may help:

Declare @dt1 datetime, @dt2 datetime

Set @dt1 = '2003-10-24'

Set @dt2 = dateadd(day,1, @dt1)

then use something like:

AND DCCntHst.SentDate between @dt1 and @dt2

I'm sure there is a much better way (and someone here will probably post a much better idea), but this should get you started.

BTW. I used the two @dt variables instead of doing something like 'between @dt1 and dateadd(day,1, @dt1)' because SQL Server will better utilize indexes if you have a Between statement with actual numbers (or variables) rather than using dateadd in the between statement. This is because SQL Server has to evaluate each row to see if it fits the dateadd criteria in the latter example, instead of utilizing an index, etc.

sara karasik SSCommitted Points: 1573 More actions · Answer 2

Use the between operator. You want all values between '2003-10-24 00:00:00' and '2003-10-25 00:00:00', meaning everything that took place that day until midnight. If you are passing in a variable, such as @date_I_want_to_use, then use

between @date_I_want_to_use and @date_I_want_to_use + 1

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

You can also do something like :

WHERE

(CONVERT(VARCHAR(10),DCCntHst.SentDate,120) = CONVERT(VARCHAR(10), @paramdatetime, 120))

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

ron k SSCertifiable Points: 6103 More actions · Answer 4

The between operator is inclusive so you do not want to use it in this case. You would need to do something like:

where... col_date >= @date and < @date + 1

Of couse your best answer is within this article by Frank Kalis:

http://www.sql-server-performance.com/fk_datetime.asp

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

Yes, since the date comparison that you are doing is for equals between the dates the above solution should do it.

WHERE

(LEFT(DCCntHst.SentDate),11) = LEFT(CONVERT(DATETIME,@paramdatetime),11))

Thanks

Prasad Bhogadi
www.inforaise.com

ron k SSCertifiable Points: 6103 More actions · Answer 6

Here's an example from the Frank Kalis article mentioned aboved. For a full discussion on working with sql server dates please read the article.

SELECT

CustomerID

, OrderDate

FROM

Orders

WHERE

OrderDate >= '19960704'

AND

OrderDate < '19960705'

This will return all rows where the OrderDate is sometime on July 4, 1996.

ron k SSCertifiable Points: 6103 More actions · Answer 7

Another example from the Frank Kalis article. How to strip the time from the date (replace Getdate() with any datetime value):

SELECT

DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

------------------------------------------------------

2005-03-23 00:00:00.000

For full explanation read the article found at:

http://www.sql-server-performance.com/fk_datetime.asp

Paula-196779 SSCrazy Points: 2575 More actions · Answer 8

Thanks everyone, I will try a few of these. I have tried the >= and <= solution but it still only brought back one time. I'll keep trying though.

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 9

have you tried

select CONVERT(DATETIME,DCCntHst.SentDate,102), CONVERT(DATETIME, '2003-10-24 00:00:00', 102), *

where....

(CONVERT(DATETIME,DCCntHst.SentDate,102) = CONVERT(DATETIME, '2003-10-24 00:00:00', 102))

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 10

Bersileus, it is not a good idea to convert the column before comparing it. This means that no index can be used to find the rows that match the where clause, so a full scan will be needed. Where clauses, as well as join ON clauses, should always be SARGable, which means they should conform to the following 'formula':

column operator expression

(They can of course also be expression operator column)

Your example uses the form expression operator expression, which means the argument is not sargable and therefore will require a full scan.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 11

thanks cris, i had to look-up the word SARGable if it really existed

The term "sargable" (which is in effect a made-up word) comes from the pseudo-acronym "SARG", which stands for "Search ARGument"

good point, thanks