I download data from a remote source and unfortunately the date field comes down as a character field - '19990507'(8 character) - I want to convert the character field to a date field. Since I am also parsing a number of other fields I used the Update command and it has worked fine except now when I need to convert a date field. I tried to use the date() function and convert the year,month and day to values as follows
datefield = (8) date
char_date = (8) character

UPDATE tablename;
SET datefield = date(val(left(char_date,4)) ,val(substr(char_date,5,2)) ,val(right(char_date,2)))

I get an error message (invalid function,procedure etc etc)


I also tried to use "{"+left(char_date,4)+"-"+subs(char_date,5,2)+;
"-"+right(char_date,2)"}" including macro substitution and about every variation of this and it doesn't work.



If I do the following I can update the table, however it sets all dates to the date in the first record. Unfortunately the dates are not all the same in the table and while it works it gives the wrong results.

use tablename
lnYr =val(left(char_date,4))
LnMo = val(substr(char_date,5,2))
LnDay = val(right(char_date,2))

UPDATE tablename;
SET newdatefield = date(lnYr,lnMo,LnDay)



I can use the scan and replace commands and solve the problem, however I really don't want to rewrite a bunch of code.

I am now appealing for help for one more knowledgeable than me on how to convert a character string to a date field using the update command. Any and all help will be appreciated. Could this be a "bug"!!!

Gaylen Jungling