473,545 Members | 1,797 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Comparing time values

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(smallda tetime, Start, 108) > CONVERT(smallda tetime,
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

Aug 25 '05 #1
6 5197
Stu
I'm confused; if both columns are smalldatetime, then why are you
converting them to smalldatetime?

Stu

Aug 25 '05 #2
On 25 Aug 2005 09:24:33 -0700, te********@hotm ail.com wrote:

(snip)
Start (smalldatetime) - the TIME the work block is begun (...)OTRateBegins (smalldatetime) (...)
If CONVERT(smallda tetime, Start, 108) > CONVERT(smallda tetime,
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)
Aug 25 '05 #3

Hugo Kornelis wrote:
On 25 Aug 2005 09:24:33 -0700, te********@hotm ail.com wrote:

(snip)
Start (smalldatetime) - the TIME the work block is begun

(...)
OTRateBegins (smalldatetime)

(...)
If CONVERT(smallda tetime, Start, 108) > CONVERT(smallda tetime,
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

Aug 25 '05 #4
On 25 Aug 2005 12:36:24 -0700, te********@hotm ail.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 misunderstandin g. 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(1 2), 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)
Aug 25 '05 #5
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

Aug 26 '05 #6

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

Aug 26 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
13103
by: Donnal Walter | last post by:
I was very surprised to discover that >>> import datetime >>> x = datetime.date(2004, 9, 14) >>> y = datetime.datetime(2004, 9, 14, 6, 43, 15) >>> print x == y True How can these two objects be considered equal? Is there a *general* way to test for date != datetime as well as 4.5 != 4.6?
13
4967
by: Dylan | last post by:
I'd like to compare two containers. They should be considered equivalent if both containers have the same number of elements with the same values, no matter what order the values are in. For instance the containers A = B = are obviously equal, but so would be
41
3912
by: Odd-R. | last post by:
I have to lists, A and B, that may, or may not be equal. If they are not identical, I want the output to be three new lists, X,Y and Z where X has all the elements that are in A, but not in B, and Y contains all the elements that are B but not in A. Z will then have the elements that are in both A and B. One way of doing this is of course...
2
3323
by: Manny Chohan | last post by:
Hi, i have two datetime values in format 11/22/04 9:00 AM and 11/22/04 9:30 AM. How can i compare dates .net c# or if there is any other way such as Javascript. Thanks Manny
19
2628
by: Dennis | last post by:
I have a public variable in a class of type color declared as follows: public mycolor as color = color.Empty I want to check to see if the user has specified a color like; if mycolor = Color.Empty then..... or if mycolor is Color.Empty then .......
5
5648
by: JL | last post by:
I need to compare two times. The problem I have is this: In my code I create a time variable using the format statement below: dim firstTime as DateTime fistTime = Format("12:00:00 AM", "T") I then store this in an Access database DateTime field. When I read it back from the DatTime field. The values do not compare.
5
3793
by: Kermit Piper | last post by:
Hello, I am comparing two date values, one from a database and one that has been converted from a hard-coded string into an actual Date type. So far so good. The problem I'm having is that one of the values comes from the database, and for existing values it works fine, but if the date doesn't exist (which will always be the condition when...
1
7683
by: psmahesh | last post by:
Hi folks, I am comparing two arrays and removing matches from the second array from the first array. Can someone take a look at this code below and mention if this is okay and perhaps if there is a better way to achieve it for(i=0;i<arrayA.length;i++){ for(j=0;j<arrayB.length;j++){ if(arrayA==arrayB)
2
3371
by: Pugi! | last post by:
hi, I am using this code for checking wether a value (form input) is an integer and wether it is smaller than a given maximum and greater then a given minimum value: function checkInteger(&$value, $checks) { $err = ''; if (!is_numeric($value) || (floatval($value) != intval($value))) { $err .= 'Input must be an integer. ';
25
12996
by: J Caesar | last post by:
In C you can compare two pointers, p<q, as long as they come from the same array or the same malloc()ated block. Otherwise you can't. What I'd like to do is write a function int comparable(void *p, void *q) that will take any two pointers and decide whether they can be compared or not. I really can't think how to do this - any...
0
7487
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7420
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7680
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5349
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4966
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3459
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1908
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
731
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.