CONVERT() makes stored procedure run slow.

[lak201]

hi all,

i am having a problem because sql does not have a "date" variable and has a "datetime" variable instead.

i have 2 tables both with datetime fields, which should actually be just dates.. i have about 2-300,000 rows in each table.

i have to do an inner join on the dates being equal( not the time)... for this i use the convert

ON Convert(Char(10),date1,102)=Convert(Char(10),date2,102)

this convert slows down my stored procedure by a lot( by 60 times atleast)

on the other hand i can try to make sure that the dates i enter always are dates and not datetimes, but i cannot absolutely guarantee that....

is there some way of making this thing faster. ie

1) is there any alternative to using the datetime variable, just the date
2) if i have to use it can i put in some kind of restriction in the table itself to make sure that it is a date and not a datetime.
3)is there some way in which i can compare just the dates and not the datetimes without using the Convert fnuction.

thanks in advance

 

[PHV]

Does your RDBMS admit this ?
ON Int(date1)=Int(date2)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lak201]

no.. and i am sorry i didnt mention it... but i am using SQL Server...

but i think i can use this

cast(date1 as int)=cast(date2 as int)...

but is this any better than using the convert function..

thanks

 

[PHV]

I don't know SQL Server but I would try cast to Long (as today is 38303), should be faster than string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLSister]

YOu could put a trigger (for insert and update) on your table that alawys changes the time to midnight. then you won't need to cast the data as anything.

Questions about posting. See faq183-874