Question Re: date / time field conversion in Technology Q&A

date / time field conversion

andy-jay51 — Wed, 28 Jul 2021 09:25:00 GMT

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

Re: date / time field conversion

DellSC — Wed, 28 Jul 2021 22:32:27 GMT

When you convert a number to text, ToText will automatically format the number to include commas as thousands separators - so it doesn't necessarily look like you think it does. You can put your current {@Pick Start} on the report temporarily to see what I mean. I would add a format string to your ToText so that the formula looks like this:

TOTEXT ({GPKNA.PKNSPS}, 0, "")

If that doesn't solve the issue, let me know as I may have a few other ideas.

-Dell

Re: date / time field conversion

andy-jay51 — Thu, 29 Jul 2021 10:32:37 GMT

Good morning Dell,

Thank you for the idea.

Above are the results I get using my existing set up as explained in my formula. I get date, start and end. Date I don't have any issues with and you can see the formulas do work and I can export manually no issues. Its when the third party software refreshes the report remotely and exports to its final destination that it is failing and giving me the error below

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

To get the time in the current format I have it in, I have created layered formulas:

Picking Start Time Code = TOTEXT ({GPKNA.PKNSPS}, 0, "")

Picking Start Code 2 = TONUMBER (Right ({@Picking Start Time Code}, 6))

Pick Start = stringvar x :=totext({@Picking Start Code 2},"000000"); time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

And that is how my time field is populated?

Many thanks,

Andy

Re: date / time field conversion

DellSC — Thu, 29 Jul 2021 13:17:40 GMT

Are you using this in the Select Expert? Or just putting the data on the report?

-Dell

Re: date / time field conversion

former_member11696 — Thu, 29 Jul 2021 13:45:02 GMT

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....