x

How to get the getdate with time part as 00:00:00.000 ?

Hi,

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 ?
more ▼

asked Apr 24, 2012 at 03:52 AM in Default

satya gravatar image

satya
361 18 18 22

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

  1. Change your query so that you are no longer doing any conversions for the comparison.

  2. Change the structure of your table to accommodate the conversion.

Solution 1:

/* Test Table & Data */
DECLARE @MyTable AS TABLE (col1 int, col2 datetime);
INSERT INTO @MyTable
        (col1, col2)
SELECT 1,'2012-04-24 00:00:00' UNION
SELECT 2,'2012-04-24 00:00:00' UNION
SELECT 3,'2012-04-25 00:00:00' UNION
SELECT 4,'2012-04-26 00:00:00' 
;


/* use a variable to cast the date correctly */
DECLARE @date datetime = CAST(convert(varchar(10),GETDATE(),112) AS date) 

SELECT  col1,
        col2
FROM    @MyTable AS MT
WHERE col2 = @date

/*or do the conversion directly*/
--WHERE col2 = CAST(convert(varchar(10),GETDATE(),112) AS date) 

Solution 2:

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.
more ▼

answered Apr 24, 2012 at 07:36 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

I would use

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)

This way you would preserve the datetime data type and no conversions required.

So your date comparison could be re-written as

WHERE datecolumn = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)
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)
more ▼

answered Apr 24, 2012 at 07:02 AM

robbin gravatar image

robbin
1.6k 1 3 5

YEah, this is much much quicker than the type cast to varchar and than back to date time.
Apr 24, 2012 at 11:22 AM Pavel Pawlowski
@Pavel - I'm going to bet the actual CAST() around GETDATE() is going to be negligible for the query (whether to varchar and back to date or direct dateadd). The main thing that @satya needs to consider is not using the functions on the columns at all - do the conversion of the variable to the data type of the column - and do that only once.
Apr 24, 2012 at 11:29 AM WilliamD

@William sure, definitelly wheter you will use CAST() or DATEADD(), DATEDIFF() in this concrete situation, there will be no difference in the speed as the conversion will be done once for the whole query.

And as you have mentioned and also as @robbin mentioned, the condition needs to be rewritten in a way that there is no function on the table column.

What I wanted to point out was, the the DATEADD, DATEDIFF conbination is much quicker in general compared to the CASTING.
Apr 24, 2012 at 12:18 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985

asked: Apr 24, 2012 at 03:52 AM

Seen: 8387 times

Last Updated: Apr 24, 2012 at 12:18 PM