Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Time Calculation
- Thread starter RTorrance
- Start date
Mike Lewis
Programmer
RTorrance,
REPLACE MyTimeField WITH MyTimeField - (4 * 60 * 60)
Does that work for you?
Mike
Mike Lewis
Edinburgh, Scotland
REPLACE MyTimeField WITH MyTimeField - (4 * 60 * 60)
Does that work for you?
Mike
Mike Lewis
Edinburgh, Scotland
- Thread starter
- #3
Mike,
no that did not work. The table that the time is in is sent to us daily from a customer. The time indicates what time our parts must be at their facility. We need to subtract 4 hours from that time to get our ship time in a program that will print out a detail report. The time field is a character field with values like 0330 or 1530. When I try to use the ctot() function, I get a function argument value, type or count is invalid error. Thanks for the suggestion. Any other help is appreciated.
Rachael
no that did not work. The table that the time is in is sent to us daily from a customer. The time indicates what time our parts must be at their facility. We need to subtract 4 hours from that time to get our ship time in a program that will print out a detail report. The time field is a character field with values like 0330 or 1530. When I try to use the ctot() function, I get a function argument value, type or count is invalid error. Thanks for the suggestion. Any other help is appreciated.
Rachael
Mike Lewis
Programmer
Rachael,
The time field is a character field with values like 0330 or 1530.
It would have been helpful if you mentioned that at the outset.
Perhaps something like this would work:
nHours = val(left(MyTimeField,2))
nMins = val(right(MyTimeField,2))
x = DATETIME(2003,12,31,nHours,nMins,0)
y = x - (4 * 60 * 60)
The actual numbers that you pass to DATETIME don't really matter -- except for the 4th and 5th parameters. The idea is that it will create a datetime variable with an arbitrary date but with the actual time that you are interested in.
I haven't tested the above code, but I think it should give you a start.
Mike
Mike Lewis
Edinburgh, Scotland
The time field is a character field with values like 0330 or 1530.
It would have been helpful if you mentioned that at the outset.
Perhaps something like this would work:
nHours = val(left(MyTimeField,2))
nMins = val(right(MyTimeField,2))
x = DATETIME(2003,12,31,nHours,nMins,0)
y = x - (4 * 60 * 60)
The actual numbers that you pass to DATETIME don't really matter -- except for the 4th and 5th parameters. The idea is that it will create a datetime variable with an arbitrary date but with the actual time that you are interested in.
I haven't tested the above code, but I think it should give you a start.
Mike
Mike Lewis
Edinburgh, Scotland
If it is stored as military time (ie. 0330) in a character field, couldn't you take the VAL(LEFT(time,2)) and substract 4. If the value is less than 0, then it would be the previous day and you would need to adjust for it, maybe by adding 24 (eg. -3 + 24 = 21 which is 9PM the previous day).
Something to the affect of:
PADL(ALLTRIM(STR(VAL(LEFT(myTime,2))-4)),2,"0"
+ RIGHT(myTimeField,2)
This is not nearly as clean as Mike's but I did sort of run it and it seemed to return what you needed.
Something to the affect of:
PADL(ALLTRIM(STR(VAL(LEFT(myTime,2))-4)),2,"0"
+ RIGHT(myTimeField,2)This is not nearly as clean as Mike's but I did sort of run it and it seemed to return what you needed.
- Thread starter
- #6
- Status
Similar threads
- Locked
- Question
