Rounding datetime values down

Hi,
I have a task that needs to filter large datasets by date, ignoring the
time. However, we do need to store times alongside the dates for other
uses.
The way that had been implemented was to convert to a varchar and then back
again e.g.
convert(datetime, convert(varchar(10), col_name, 103), 103)
However, this results in an unacceptable performance hit.
Is there any better way of achieving this, or am I stuck with having an
extra column which can be populated at the same time (e.g. by a
insert/update trigger)?
John McLuskyJust query the DATETIME column as a range. For example, for today's date:
SELECT ...
FROM YourTable
WHERE col_name >= '20050608'
AND col_name < '20050609' ;
David Portas
SQL Server MVP
--|||David Portas wrote:
> Just query the DATETIME column as a range. For example, for today's
> date:
> SELECT ...
> FROM YourTable
> WHERE col_name >= '20050608'
> AND col_name < '20050609' ;
Hi David,
Thanks for the suggestion. It may be possible to implement inside the
stored procedures that we're using at the moment and I'll look into that
tomorrow when I'm back in the office.
However, if there's a possibility of converting the dates directly (so they
can be used in a view, for example) that would be the best solution.
At the moment, the new column looks like the easiest option!
John.|||Try,
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050131'
select c1, ..., cn
from dbo.t1
where c2 >= @.sd and c2 < dateadd(day, 1, @.ed)
AMB
"JM" wrote:

> Hi,
> I have a task that needs to filter large datasets by date, ignoring the
> time. However, we do need to store times alongside the dates for other
> uses.
> The way that had been implemented was to convert to a varchar and then bac
k
> again e.g.
> convert(datetime, convert(varchar(10), col_name, 103), 103)
> However, this results in an unacceptable performance hit.
> Is there any better way of achieving this, or am I stuck with having an
> extra column which can be populated at the same time (e.g. by a
> insert/update trigger)?
> John McLusky
>
>|||> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
You already have that solution. One possible improvement is to cast to INT
and then back to DATETIME. However, the range query method is better because
it can make full use of an index on the column and avoids unnecessary type
conversions. This can make a BIG difference to performance. Of course you
could consider an indexed view or indexed computed column but that seems
redundant to me if your data can be accessed via an SP.
David Portas
SQL Server MVP
--|||I don't understand. You first post asked for code suggestions how to do this
in a efficient way.
Then you imply that you will have difficulties to implement that. Can you ch
ange your code or not?
If you can, do it the right way.
I can imagine adding a computed column to the table and index that column. W
ould I do that? No. You
would still have to adapt your code for the dirty solution, so better to do
it right up front. :-)
Some elaborations on the subject (showing some options, explaining why IMO D
avid's suggestion is the
ay to go):
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JM" <john@.demon.invalid> wrote in message news:3gontsFdkcgoU1@.individual.net...red">
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the sto
red procedures that
> we're using at the moment and I'll look into that tomorrow when I'm back i
n the office.
> However, if there's a possibility of converting the dates directly (so the
y can be used in a view,
> for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||The easiest way is rarely the best way.
In this case though, the easiest is the best.
Try David's solution.
Maybe you didn't give enough information on what you are trying to do for
the contributers here to find the best solution.
"JM" <john@.demon.invalid> wrote in message
news:3gontsFdkcgoU1@.individual.net...
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the
> stored procedures that we're using at the moment and I'll look into that
> tomorrow when I'm back in the office.
> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||Hi all,
I agree, the (truly) best solution would appear to be David's, and I will
see what I can do with our SPs tomorrow.
When I said 'best' before, I really meant easiest from an implementation
point of view - messy, but easy.
Thanks for all the suggestions. I guess I'm surprised that there isn't a
simple 'round to midnight' function that can be used!
John.
Raymond D'Anjou wrote:
> The easiest way is rarely the best way.
> In this case though, the easiest is the best.
> Try David's solution.
> Maybe you didn't give enough information on what you are trying to do
> for the contributers here to find the best solution.
> "JM" <john@.demon.invalid> wrote in message
> news:3gontsFdkcgoU1@.individual.net...