Query Help

Question

Post reply

Query Help

Santhosh Kumar

Right there with Babe

Points: 766
More actions
October 4, 2005 at 9:38 am

#152570

Hi guys,
The below part is the where condition of a query is there any alternate
way to compare that directly
(We store the created date as datetime but while comparing we have to
compare till hours only.)
Where DATEPART( yyyy,
CreateDate ) = @SelectYear
AND DATEPART( mm, CreateDate )
= @SelectMonth
AND DATEPART( dd, CreateDate )
= @SelectDay
AND DATEPART( hh, CreateDate )
= @SelectHour
Can the above be replaced as
cast(createdate,datetime) = cast('12/12/2005 1:00 PM',datetime) -- this is
not working for me
but the comparision has to be done only till the hour not till the minutes
and seconds.
Thanks for your help
with smiles
santhosh

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

where CreateDate >= '12/12/2005 1:00 PM' and CreateDate < '12/12/2005 2:00 PM'

Shabnam Gupta SSC Rookie Points: 31 More actions · Answer 2

Try this

WHERE convert(varchar(10),created_date,101) + ' ' +

convert(varchar(2),datepart(hh,created_date)) + ':00 ' +

CASE WHEN datepart(hh,created_date) >= 12 THEN 'PM' ELSE 'AM' END >= @inputdate

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This is a great way to make sure performance is non existant. This force a scan of the table/index because of calculations on a field. The between version can use an index seek if an index is present.

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 4

Hi Guys,

Thanks for your reply I got some other solution for this problem as given below.

DATEDIFF(hour,cast(createdate as datetime),cast(@SQLSelectBeginDate as datetime)) = 0

HTH

with smiles

Santhosh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Same performance problem, this won't use an index. Don't come back crying in 3 years when the query runs in 3 minutes when it should be in MS.

Ray M SSC-Insane Points: 21093 More actions · Answer 6

As remi is stating, you should create a begin and end date parameter instead of using datepart.

set @Begindate = '10/07/2005 10:00:00'

set @Enddate = '10/07/2005 11:00:00'

Select ...

From Mytable

Where CreateDate >= @Begindate and < @EndDate

Your performance will be terrible if you do you as you have presented in your example.

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 7

Interestingly this query takes much time to execute

DATEDIFF(hour,createdate,@SQLSelectBeginDate) = 0

than the below query

Where DATEPART( yyyy,

CreateDate ) = @SelectYear

AND DATEPART( mm, CreateDate )

= @SelectMonth

AND DATEPART( dd, CreateDate )

= @SelectDay

AND DATEPART( hh, CreateDate )

= @SelectHour

any clues why... its killing me

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

October 7, 2005 at 1:11 pm

#596017

Seriously, try reading what Ray and I said.

Ray M SSC-Insane Points: 21093 More actions · Answer 9

of the two options you have tried, they both are wrapping a function around a table column, which FORCES sql server to do a table scan, it cannot/will not use a table index/or statistics to perform your search.

You seriously need to change your query as Remi, and I have presented.