Posted by Hugo Kornelis on 08/26/05 00:11

On 25 Aug 2005 12:36:24 -0700, teddysnips@hotmail.com wrote:

(snip)
>I don't particularly want to post DDL and INSERT statements, as there's
>no real data to play with at the moment, and the table relations are a
>good deal more complicated than I've let on.
>
>However, suffice it to say that the datepart of the OTRateBegins is
>entirely disposable - I'm only interested in the timepart element. So
>I just want to ignore the datepart.

Hi Edward,

The reason I asked for DDL and INSERT statements is to make sure that
there can be no misunderstanding. It may be because of me not being a
native English speaker, but I'm still not sure if your OTRateBegins
column will contain a time combined with an (irrelevant) date, or if
they will contain only the time (*).

(*) SQL Server will of course always store a date - "only the time"
means that you don't provide any date; in that case, SQL Server will use
the default date (January 1st 1900).

Anyway, here's a more generic answer for comparisons where you want to
compare only the time portion of the datetime:

- If both Column1 and Column2 contain only a time, you can compare them
with a straight comparison (Column1 > Column2); the advantage is that
the optimizer can choose to use any index on either or both of these
columns.
- If one of the columns contains a date + time and the other contains
only the time, you'll have to strip the datepart of the column with date
(Column1 > CONVERT(char(12), Column2, 114) or CONVERT(char(12), Column1,
114) > Column2); in this case, the optimizer can still use an index on
the column that has only the time - the other column is used in a
function, which precludes the use of an index.
- If both columns containt date and time, you'll have to strip both
(CONVERT(char(12), Column1, 114) > CONVERT(char(12), Column2, 114)); the
disadvantage is that the optimizer can't use the indexes on any of these
columns.

If you don't need millisecond precision, you can also use
CONVERT(char(8), Column1, 108).


>In terms of what I personally wish to do (I'm actually developing a UDF
>to return accumulated minutes multiplied by the correct OT rate
>multiplier)
(snip)

I was afraid that it'd be something like that. Yet another reason to
post the CREATE TABLE statements plus some sample data (can be made up)
and expected output, plus the code you currently have.

The use of IF in your original post suggests that you process your input
table row by row. In 99% of all cases, a set-based solution is faster,
shorter, easier to understand and hence also easier to maintain. If you
provide some more information, I (and maybe others as well) can take a
look at your logic and try our hand at converting it to a set-based
version.

(snip)
> As I suggested in my original post, I *could* strip out
>the HOUR and MINUTE values using the DATEPART function, and do some
>rather more complex comparisons using them, but that seems rather
>inefficient.

Ugh! Please don't go there - why would you even want to write messy and
complex code when a simple comparison (with CONVERT, if you have to use
it) will do?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]