Formatting a character date field

[Frank Lucenti]

During an import process I have a filed called �date� that comes in as a character field �6 long�. It is formatted in yymmdd. Is there a function to look at a character field that has numbers like this date field and convert all dates to the �highest date� in that field. So if had 021225 and 021224 in one field can I convert all dates in that field to the highest date and formatted like this.. 02/12/25.
One step further: I would like to convert the character date format of yymmdd to mm/dd/yy and convert all dates in that field to highest date.
I hope that I explained this properly.

[Tom Cone Jr]

RE: Formatting a character date field


Frank,

I think you're talking about several distinct steps, rather than a single function.

If it were me, I'd approach it this way:

1) import the data to a temporary table
2) add a new char type field to the temp table, 8 wide
3) use an update operation to convert YYMMDD to MM/DD/YYYY in the new char field (this will involve an expression using the substr() function 3 times)
4) add a new date type field to the temp table
5) use an update operation to convert the new char type field to a date type and store it in the new date type field. (this will involve the ctod() function)
6) sort the temp table by date, descending
7) use the value in the most recent date field as the source and update the other date field values to match
8) finally, append the desired fields to new records in your primary table

-- tom

[Tom Cone Jr]

RE: Formatting a character date field


Frank, on re-reading my post I see an error. In step 2, the new char type field must be 10 wide, or there won't be room for the slashes. -- tom

[Frank Lucenti]

RE: Formatting a character date field

I now have a character field formatted xx/xx/xx
(the / / ) are a mask. So the field is mmddyy. I split up the import field up 2-2-2(xx xx xx) adn imported them into 3 fields. Then used F1+F2+F3 to get the mmddyy.
I have to dates in the character field. 050402 and 050502. I want to fill this field with the greater of the two.
Tom: I'm being thick.....on the back half.

Thanks
Frank

[CALocklin]

RE: Formatting a character date field

I was hoping to help with this but you've got me completely confused. How can you have a date field 6 long formatted yymmdd with 2 (or more?) dates in it? Are these dates in one field of one record or two fields of one record or one field in two different records?

You also said you want to convert all dates to the highest date in that field. Convert all what dates? Presumably the other dates in that "field"?

The best I can guess is that you have a character field like this:
021225 021203 020930
and you want to convert it to a character field that looks like this:
12/25/02 12/25/02 12/25/02

If you want to actually convert the character values to date values (not just to LOOK like date values but to actually BE date values) then a new field will be required for each resulting date value.

Please clarify or provide an example.