Comparing time values

[teddysnips]

I have a table called WorkItem. It models a chunk of work done during
a working day.

It has two columns that I'm interested in:

Start (smalldatetime) - the TIME the work block is begun
Duration (int) - the duration in minutes of the work block.

In another table called OvertimeRates I have information about rate
multipliers and a column that tells me the TIME that the rate
multiplier kicks in.

e.g.

OTRateBegins (smalldatetime)

In terms of calculating whether a particular work block starts after
the OTRateBegins, I could (I presume) do something like:

If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
OTRateBegins, 108)

However, would I be better off using DATEPART functions to get the hour
and minute parts of both the Start and OTRateBegins, and using them
instead? For some reason, (probably paranoia!), I am suspicious of the
CONVERT function.

Apologies for not posting DDL, but I felt that the situation didn't
really warrant it.

Thanks

Edward

[Stu]

I'm confused; if both columns are smalldatetime, then why are you
converting them to smalldatetime?

Stu

[Hugo Kornelis]

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.

Best, Hugo
--

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

[teddysnips]

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

[Hugo Kornelis]

On 25 Aug 2005 12:36:24 -0700, te********@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)

[Stu]

OK, now I'm even more confused. Too much time behind my keyboard, I
suppose. Hugo, why can't he do a simple compare if both columns
contain a date and time? Here's my example:

DECLARE @Start smalldatetime
DECLARE @OTRateBegins smalldatetime

SET @Start = '6/1/2005 4:00:00 AM'
SET @OTRateBegins = '5/30/2005 4:00:00 AM'

IF @Start > @OtRateBegins
PRINT 'Start > OTRate'
ELSE
PRINT 'SPLAT'

I guess the question is: what does OTRateBegins represent? Is it the
time of the day that overtime kicks in? Or is it a calendar date and
time that represents the overtime phase of a project (e.g., you have a
contract for 100 hours, and you want to be paid the overtime rate if
you go past the 100th hour)?

Still confused.
Stu

[teddysnips]

Stu wrote:

OK, now I'm even more confused. Too much time behind my keyboard, I
suppose. Hugo, why can't he do a simple compare if both columns
contain a date and time? Here's my example:

DECLARE @Start smalldatetime
DECLARE @OTRateBegins smalldatetime

SET @Start = '6/1/2005 4:00:00 AM'
SET @OTRateBegins = '5/30/2005 4:00:00 AM'

IF @Start > @OtRateBegins
PRINT 'Start > OTRate'
ELSE
PRINT 'SPLAT'

I guess the question is: what does OTRateBegins represent? Is it the
time of the day that overtime kicks in?
Yes, which is why I can't do a simple comparison as above Or is it a calendar date and
time that represents the overtime phase of a project (e.g., you have a
contract for 100 hours, and you want to be paid the overtime rate if
you go past the 100th hour)?

No!

Thanks Stu and Hugo for your work on this. As it happens, Hugo nailed
it in the previous post. For the comparison I'm trying to do, it is
only the time component that matters, so the use of 108 (I don't need
milliseconds!) for the CONVERT style will be fine.

As Hugo guessed, I'm NOT using a SET based solution, because the
requirements are too complicated for my tiny brain! I'm going to
persevere with my design, and see how it performs. If it's a
three-legged dog, I'll come back here with my begging bowl, and some
DDL and INSERT statements and tax your generosity some more.

But in the meantime, THANK YOU to Stu and Hugo!

Edward