In one of my query I have to check the startdt column with getdte(), but the startdt datetime field saving time part as zero like 'yyyy-mm-dd 00:00:00.000' (like 2012-04-25 00:00:00.000).
for this converting the getdate() format we are using : cast(convert(varchar(20),getDate(),101)
It effects the performmance and it is causing Deadlocks, So any one please suggest how to check the startdt with getdate() with out effecting the performance.
can I use convert(varchar(10),startdt,101) = convert(varchar(10),getdate(),101) is it increases the performance ?
Date querying is something that a lot of people stumble over because of the difficulties of the data-types offered by SQL Server.
As of SQL 2008 you have the data-type "date", which stores a date without any time. I'm not sure if you have that option, I'm guessing no.
What you need to realise here is that using (just about) any functions on a column within your select statement is going to pretty much guarantee performance problems with any tables that are larger than a couple of hundred rows.
The issue you are likely experiencing is due to the date conversion which stops SQL Server (or the Query Optimiser to be more precise) from using the table, index and statistics data properly to access the data in the best way.
You have two options to get around your issue which don't require much work.
Add a computed column to the table that casts that date column to the same data-type as you need (and index on that). Your query can then remain unchanged, but SQL Server will then use the computed column behind the scenes to perform the query.
You mentioned you were deadlocking on the table. I would suggest that you take a look at creating some indexes to assist the queries you have. If you need help with that, post the table structure and the queries that run against it on here and we'll help identify indexes that can help.
answered Apr 24 '12 at 07:36 AM
I would use
This way you would preserve the datetime data type and no conversions required.
So your date comparison could be re-written as
This way you could have an index seek/scan (if you have one). If you do not have an index on the datecolumn, you should add one to prevent the delays (covering index could be more fruitful)