character to date conversion

[m.lange]

I've moved an 18,000 record database to Alpha and have one field with an inconsistent date format. The current Alpha field type is set to character. (I don't want to change to date until I know I won't corrupt data) Some records are mm/dd/year but many records are an 8 character string (yyyymmdd). I would like to convert all to the mm/dd/yyyy format. It looks like CTOD may be the way to go? Any suggestions on how to selectively convert the appropriate records and the exact format of the CTOD function for this situation would be appreciated!

[MikeC]

Re: character to date conversion

Mike,
here is link to the date and character functions---many ways to obtain what you require, but some of them depend upon what your data is and if it varies...and when it varies, where. Also depends if this is a one time conversion or an ongoing one.

http://support.alphasoftware.com/alp..._Functions.htm
http://support.alphasoftware.com/alp..._Functions.htm

Here is an example of what would work for many instances but you will have to decide if it accounts for what you have in your data.

Place it in the interactive window to test out various data.

your_Date_field="20091204"
rst=if(isdate(your_Date_field), ctod(your_Date_field), if(len(your_Date_field)=8, stod(your_Date_field), ""))
?rst
= {12/04/2009}

your_Date_field="12/04/2009"
rst=if(isdate(your_Date_field), ctod(your_Date_field), if(len(your_Date_field)=8, stod(your_Date_field), ""))
?rst
= {12/04/2009}

[Mike Wilson]

Re: character to date conversion

Mike,
dulpicate your table or back it up first. Then add a new field Character,10, to your table for converting the dates. In the Field Rules, for Field type, make it calculated and place this expression on it:

IIF(CHR(47) !$ DATE_OLD,SUBSTR(DATE_OLD,5,2)+CHR(47)+SUBSTR(DATE_OLD,7,2)+CHR(47)+LEFT(DATE_OLD,4),DATE_OLD)

This uses the name of the previous date field being named date_old. Change the field name in the table or in the code to fit your circumstances.

Then in the control panel, highlight the table, right mouse click and choose Utilities >> Recalculate Calc Field (Field Rules). This will add the dates - mm/dd/yyyy format. Then just go into the table and change the character type to date type and you will now have a date field with mm/dd/yyyy dates.

[MikeC]

Re: character to date conversion

Hi Mike (Wilson this time!),

Thought about concatenation but seeing as how it is an unknown as to how the year is displayed (2009 or 09--Mike just states "year"), the built-in function of ctod() will take either. Of course adding this to yours would produce the same results then as mine too. I also wondered about the field and what if it did not have a correctly date formatted field--the reason I added the isdate() and the len()=8.

But I do enjoy seeing how others think about the same problem!!

[Mike Wilson]

Re: character to date conversion

MikeC,
Hi. Yes it is fun to see your and others' strategies. I didn't catch that is was mm/dd/YEAR. I read that as mm/dd/yyyy. Oops! I guess it's best at this point to just say goodnight...

[m.lange]

Re: character to date conversion

THANKS to the "Mikes". Mike Wilson, yes, you interpreted my original post correctly, the "yyyy" did indicate a four character format for the year. Your instructions were excellent and the code worked perfectly. THANKS to both of you for replying.

[neecy]

Re: character to date conversion

I am having a similar problem. I am trying to convert from character to date format. I have tried using several different updates to do so. Please find attached a sample of what I am trying to do. The first field is the character format of date. The other fields are many tests I am doing to make the conversion.

[Stan Mathews]

Re: character to date conversion

See screenshot.

[Stan Mathews]

Re: character to date conversion

The suggested expression is for the ndate field making it calculated. The stod() function doesn't like null character strings so a better expression might be

if(alltrim(Cdate_Date_)>"",stod(alltrim(Cdate_Date_)),{})

[neecy]

Re: character to date conversion

That worked perfectly!! :)

Thank you so much. :)