Subtract Hour or Convert Time Zone 1

[brizaybrizoke]

The field I need to modify is a time formula, {@Time_Formatted}

The formula I currently have in place is for this field is,

{Ordhead.ORDHEAD_ORD_TIME}[1 to 2] + ":" +
{Ordhead.ORDHEAD_ORD_TIME}[3 to 4] + ":" +
{Ordhead.ORDHEAD_ORD_TIME}[5 to 6]

The time zone that this field pulls is EST and I need it to be CST.

My options seem to be 1)reduce the time by an hour or 2)convert time to CST.

Help with either would be greatly appreciated.

Thanks in advance

 

[brizaybrizoke]

I am using Crystal 8.5

 

[Madawc]

To adjust a time you use 'DATEADD', something like

DateAdd("h", -1, {Ordhead.ORDHEAD_ORD_TIME}

Incidentally, the display formula could be done more conveniently using ToText, something like

ToText({Ordhead.ORDHEAD_ORD_TIME}, "hh:mm:ss"

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[brizaybrizoke]

I tried the dateadd function and it tells me that a date is required where I have {Ordhead.ORDHEAD_ORD_TIME}. I typed 'date' in front of the Ordhead field and it then wanted me to close the parenthetic after the word 'date' stating that the remaining text did not appear to be part of the formula.

I also tried the totext function and it states that I've provided too many arguments.

 

[lbass]

You have to use a date in the datetime field to add hours. If you have a date field that corresponds to the time field, please identify it. If not, please clarify whether the hours always fall within the same day, so that adding an hour would not change the date. So your time field itself is a 6-character string to begin with--before formatting?

-LB

 

[brizaybrizoke]

REL DATE & REL TIME = CST
BM DATE & BM TIME = EST

I am trying to adjust BM TIME aka {ORD_TIME}
The corresponding date field is BM DATE aka {ORD_DATE}

The only instance in which the time adjustment to {ORD_TIME} would affect {ORD_DATE} would be between 000000 - 010000

 

[lbass]

First create these formulas, assuming that both date and time fields are of string datatype:

//{@bmdate}:
date(val(left({table.bmdate},4)),val(mid({table.bmdate},5,2)),val(right({table.bmdate},2)))

//{@bmtime}:
time({Ordhead.ORDHEAD_ORD_TIME}[1 to 2] + ":" +
{Ordhead.ORDHEAD_ORD_TIME}[3 to 4] + ":" +
{Ordhead.ORDHEAD_ORD_TIME}[5 to 6])

Then use the dateadd formula:

dateadd("h",-1,datetime({@bmdate},{@bmtime}))

In later versions there is a function called shiftdatetime() that allows you to adjust for time zones, including daylight savings, but I'm not sure that was available in your version.

-LB

 

[brizaybrizoke]

Thank you so much lbass! All three formulas worked. You've made me a hero at work!