date / time field conversion

[#1]

Good morning,

In our database, we have a start and end field (datetime) which both fields are formatted as 20210708095000 which equates to YYYYMMDDHHMMSS. I want to separately extract a date and time for each using a formula.

To extract date I use:

If {GPKNA.PKNSPS} < 19590101 then Date (1959, 01, 01) else

Date ( Val (ToText ({GPKNA.PKNSPS}, 0 , "") [1 to 4]),

Val (ToText ({GPKNA.PKNSPS}, 0 , "") [5 to 6]),

Val (ToText ({GPKNA.PKNSPS}, 0 , "") [7 to 8]) )

To extract time, I create the below for both start and end:

I create a formula '@Pick Start' and convert start field to text = TOTEXT ({GPKNA.PKNSPS})

then use:

stringvar x :=totext({@Pick Start},"000000");

time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

This works in Crystal no issues

However we use an automated bit of third party software to refresh this each evening and export the dataset into Excel but it wont allow for exporting to to getting the following error:

"Specified cast is not valid.Couldn't store <30/12/1899 00:06:18> in @Pick Start Column. Expeted type is TimeSpan"

I think I may have approached the time formulas incorrectly on the time fields, is anyone able to advise a different approach?

Have a great day!

Andy

[former_member11696]

It can cause an error if the default date format on the other PC is not the same as your default.

Or if you used a Custom date file option or if the default is set to System.

Likely cause is differences in default date settings....