Extract Date from DateTime 1

[benvegiard]

Hi,

I'd like to delete all records from a table that have today's date. In the table, the time is also stored, so

WHERE StatDate = GetDate() or
WHERE StatDate = '2003-07-23'

matches 0 records since it compares to both date AND time. I can use CONVERT with a style to strip out the time portion, but it's messy and difficult to remember the technique.

What is the easiest way to compare only on the date portion of a DateTime?

Thanks,
Ben

 

[NoCoolHandle]

My favorite strategy for this is to just use a Between clause

so what you would see is
where statdate between '2003-7-23' and '2003-7-24'

This reduces issues with times not equal to the millisecond after midnight, or worse casting an entire col (which will cause a full table scan) to text

Rob

 

[sense]

I find DateDiff a very useful function.

Here you would say "where DateDiff(dd, StatDate, getDate()) = 0"

 

[NoCoolHandle]

WARNING, WARNING, WARNING

BE VERY CAREFULL doing what the above post suggests!!!

It [red]("where DateDiff(dd, StatDate, getDate()) = 0" )[/red] will force a table scan EVERY TIME!!

If performance isn't an issue and making your life as a coder is.. go with it.. If not DONT DO THAT!

This strategy in essence invalidates the search arguement.

 

[ncedddf]

You could also use
WHERE convert(Varchar(10),StatDate,101) = convert(Varchar(10),GetDate,101)

 

[sense]

Hmm, NoCool, you raise a good point re: performance issues between DateDiff and BETWEEN... Never woulda foreseen that. In fact I would have expected the opposite considering the datatype conversion.

Regardless, don't forget that BETWEEN is inclusive and converting a string without a specified time to a datetime datatype defaults to a time of 00:00:00...

so...

"StatDate between '2003-7-23' and '2003-7-24'"

is the same thing as saying

&quot;StatDate >= '2003-7-23 00:00:00' and StatDate <= '2003-7-24 00:00:00' &quot;

 

[NoCoolHandle]

Sense..
in essence[blue]
StatDate >= '2003-7-23 00:00:00' and StatDate <= '2003-7-24 00:00:00'[/blue]
or
[blue]StatDate between '2003-7-23' and '2003-7-24'[/blue]

doesn't doesn't convert or change any of the column data.

It just searchs a valid range of dates. Indexes will be used if they would help.

If you do any sort of calculation or run some kind of opperation on a column before you test it, it will need to process every row and then compare it.. THerefore the table scan.

ie[blue] select * from X where col1 + 1 = 5[/blue]
will cause a table scan as we have to add 1 to the col to see if it = 5
rewrite the query
[blue]select * from X where col1 = 4[/blue]
You bring back the same number of rows, but because you didn't add 1 before you test it can use an index.

In much the same way any time you convert or cast the col it will force sql to do that on every row before it can compare the value..

Hence the problem with datediff .. In this case it has to pass the value in and then return if it is = to 0 if not don't include in the results set..


HTH

Rob

 

[clarkin]

its a valid point about not using functions as it means indexes are ignored. However there's a good chance the date column isn't indexed as it is its always a good bet to try both ways and see which is faster for you.

One point about BETWEEN - its inclusive as mentioned - so if you really only want datetimes that fall on one particular day you really should use:

StatDate >= '2003-7-23 00:00:00' and StartDate < '2003-7-24 00:00:00'

if you use BETWEEN you will get midnight 24th rows too - which is common as 00:00:00.000 is the time you get if you create the datetime by only feeding it a date

good luck, let us know what you use


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].