Hugo Kornelis wrote:
On 25 Aug 2005 09:24:33 -0700, te********@hotmail.com wrote:
(snip)Start (smalldatetime) - the TIME the work block is begun
(...)OTRateBegins (smalldatetime)
(...)
If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
OTRateBegins, 108)
Hi Edward,
If both columns store just a time (or rather: the datepart is left at
the default value), you can use a simple comparison:
IF Start > OTRateBegins
If either or both sport a date value as well, you'll need another
solution. The convert might work (no reason for your paranoia), but
there might be better solutions as well.
If you post CREATE TABLE statements, some INSERT statements with sample
data, and expected output, it'll be easier to help you.
Thanks Hugo.
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.
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) I'm not interested in the datepart of the Start column, but
in fact the datepart of this column is crucial, as it tells one when
the block of work was done (there are different multipliers for
different time periods and different days).
Hence the reason for the CONVERT function (as Stu asked), which I was
using merely to expose the timepart of the two fields. Is there a
better way? 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.
I dunno. As usual, I'm probably ignoring the obvious and elegant in
favour of the simple and quick. It was like that when I was a C
programmer - I never could get the hang of doing stuff in-line.
Anyway, many thanks for your help.
Edward