Convert a alfa filed into date field 1

[UAMI]

Hi!

I have a pf file with a alfa field:
DTAIN 26

that was bad created, because it supouse to be a date field or a timestamp field, so that was possible to extract register by date. Because it it a alfa field, it isn't possible. This field was being field from a access aplication and because each computer had varios regional settings dates, once are:
2003-08-28-10.35.51.000000 and other's
05-11-2003 16:31:04

Now this field is needed and i have to convert it into a a date or timestamp field and all in the same *iso format (ymd).

How can i do it?

 

[arrow483]

You could do something like this

D date D DatFmt(*ISO)

If %subst(dtain:5:1) = '-'
Eval date = %date(%subst(dtain:1:10):*ISO)
EndIf
If %subst(dtain:3:1) = '-'
Eval date = %date(%subst(dtain:1:10):*MDY- EndIf

You can do the same kind of thing for timestamps

 

[UAMI]

I realy need a timestanp fild, so i done it like you suggested but now i have problem's in compiling the program. I declared a timestamp variable and tain is my alfa field (26 positions).

DWDTAIN S Z


IF %subst(dtain:5:1) = '-'
Eval WDTAIN =%TIMESTAMP(%subst(dtain:1:10):*ISO)
EndIf

If %subst(dtain:3:1) = '-'
Eval WDTAIN= %TIMESTAMP(%subst(dtain:1:10):*YMD)
ENDIF


The compilir gives me the following message:
"The operand is too small to contain a complete timestamp"

One more sugestion?

 

[arrow483]

%timestamp is not as forgiving as %date. The field being passed to it must already be in *ISO format (26 chars long).

So the first Eval would be
Eval WDTAIN =%TIMESTAMP(dtain:*ISO)
because the %subst only included the 1st 10 positions.

For the second situation you need to reformat that into ISO

Eval DTAIN = (DTAIN:7:4) + '-' + (DTAIN:1:5)
+ '-' + (DTAIN:12:2) + '.' + (DTAIN:15:2)
+ '.' + (DTAIN:18:2) + '.000000'
(and then)
Eval WDTAIN =%TIMESTAMP(dtain:*ISO)

 

[UAMI]

Thank's arrow483, your tip was very important. I had to to some changes: For the eval expression i had to work with a alfa variable (wdtain2, 26) and then move it to the timestamp field dtain, otherwise i could't compile the program.


EVAL WDTAIN2=%SUBST(DTAIN:7:4) + '-' +%SUBST(DTAIN
:1:5) + '-' +%SUBST(DTAIN:12:2) + '.' +
%SUBST(DTAIN:15:2) + '.' +%SUBST(DTAIN:18:2)
+ '.' + '000000'
MOVEL WDTAIN2 DTAIN