Character to date conversion

[Stan Mathews]

I realized today that there is no native Alpha function to convert a string in "YYYYDDMM" format (like the result of cdate(date_value)) into a date. In other words I don't believe there is an inverse/reverse of the cdate() function like the corresponding

dtoc() and ctod() functions

I would be glad to be shown to be wrong, again, and would welcome suggestions for the simplest expression which would perform the manipulations involved. I have experimented with several variations involving left(), right(), substr(), *transpose() - nested four deep with a mask, and they all seem rather ugly.

Hoping I have missed the obvious ...........

[Stan Mathews]

RE: Character to date conversion

Couldn't even get the "YYYYDDMM" right, should be

"YYYYMMDD"

[davej]

RE: Character to date conversion

Stan,I might have found a function that comes pretty close to what you want.I edited a table and added two fields - testchardate (C10) & testdate (D). I set testdate as a calculated field with the calculation of: ODBC_DT_TO_D(TESTCHARDATE). In testchardate I entered 2005-06-17 and the calculation returned 06/17/2005.Hopefully this will give you a starting point?Dave

[Stan Mathews]

RE: Character to date conversion

That's something like I had come to

odbc_dt_to_d(mask(alltrim(tessd)," - - "))

where tessd is an eight digit character value

tessd = "20050617"
? odbc_dt_to_d(mask(alltrim(tessd)," - - "))
= {06/17/2005}

Thanks for your response. Still seems silly, with all the functions available, that there isn't something direct like

cdt_to_d(date_string) where date_string is in the format "YYYYMMDD"

Thanks again.

[davej]

RE: Character to date conversion

Put it on the Wish List forum. Selywn is continually adding new functions.

Dave

[csda1]

RE: Character to date conversion

Stan,

One of my 1st functions I wrote in A5 was a DATEC() function which mimics the Alpha Four function. It does all that you want, is American and European style compliant and converts Dates in Numeric, Character, Blob and Date formats to a Date format! Conversion from Date to Date is easy, the others just a bit harder. So, you just pass it any argument, and you get back a date (or blank date if error).

I sent you via email a compiled version and document for your use and edification.

Regards,

Ira

[Tom Cone Jr]

RE: Character to date conversion

Here's another approach:

begin_dt_str = "20050617"

new_date = ctod(substr(begin_dt_str,5,2)+"/"+ right(begin_dt_str,2)+"/"+ left(begin_dt_str,4))

?new_date
= {06/17/2005}


Doing this you'd have to test for a valid date after ctod() does it's work. The string may contain numerals that don't match a valid date. In this case I think ctod() will return a null (empty) date value.

-- tom

[Stan Mathews]

RE: Character to date conversion

Thanks Tom. I have been using something similar.

The data I work with comes from outside sources in various formats, the piece in question is a text file which I import into a temporary .dbf and then append to a live file while manipulating such things as this character to date conversion. I often solve the translation, for the moment, to move on with a project and later review the process to find faster or more straightforward approaches.

Such a review is where I noticed the seeming "clumsiness" of having to pick apart and reassemble a simple 8 character string which is in the same format as the result of a cdate() expression.

Thanks again, as always.

[Stan Mathews]

RE: Character to date conversion

I've already responded to Ira via email but want to publicly acknowledge his contribution as I have the others.

Any time you have a chance to sample his edification, don't miss it!

[Raymond Lyons]

RE: Character to date conversion

Is it in the code archive, where at least some of us would appreciate it being? Of course if Ira does not want it to be there, that's his perogative, I guess.

Also, why doesn't Alpha Five such a function, especially if Alpha Four had one?

Ray

[csda1]

RE: Character to date conversion

Ray,

Actually, I have no idea why Alpha 5 does not have the function, except that a later version of Alpha 4 added it after Alpha 5 was initially created.

I can make one for the code archive, but it will be a stripped down version.

The issue for me is, that, many of my functions have a lot of error checking and are extremely robust and dynamic, and these are techniques that I'm not ready to generally share, but that will be available as a function library in the future that can be purchased.

Plus, with the complexities that I add in, my code would generate more questions from many newbies than I am prepared to handle.

Regards,

Ira

[Raymond Lyons]

RE: Character to date conversion

Ira,

Thanks. I understand and would look forward to a reasonably priced function library from you.

A comment on your last sentence, which was: "Plus,with the complexities that I add in, my code would generate more questions from many newbies than I am prepared to handle." I kind of doubt that this is true. Whenever I have given out anything that is complex (not very often, admittedly!) there have been zero questions from newbies.

Again, thanks.

Ray

[Jerry Gray]

RE: Character to date conversion

Ira,

Why not just a couple of very simple functions:


For an 8 charcter date value:

FUNCTION ymdctod as D (in_date as C)
ymdctod = ctod(substr(in_date,5,2)+"/"+substr(in_date,7,2)+"/"+substr(in_date,1,4))
END FUNCTION

?ymdctod("20050401")
= {04/01/2005}


For a 6 character date value:

FUNCTION yymdctod as D (in_date as C)
yymdctod = ctod(substr(in_date,3,2)+"/"+substr(in_date,5,2)+"/"+substr(in_date,1,2))
END FUNCTION

?yymdctod("050401")
= {04/01/2005}

Both of these test out fine and if they do the job, why not just keep it simple?

Jerry Gray

[csda1]

RE: Character to date conversion

Jerry,

Why not just keep it simple? I'll give you a simple answer!

OK, I lied, and have written a 30 page exposition, but hopefully it will give you and other's insight.

- - - - - - - - - - - - - - - - - - - - - - - -

Why do you have 2 separate functions, 1 for 6 character and 1 for 8 character? No one wants to remember or have to have separate conversions based upon a specific structure of a parameter. Yes you can do this quick & dirty, and yes it works, but if you try to use it in the future, it's always taking care of special cases.

And suppose you feed the function a date in the format of "mm/dd/yyyy". Wouldn't it be nice if you recognized that and just did a ctod(), rather than having your code do different conversions based upon data types and data formats?

And yes, you can do it with a simple 1 line function, but what about invalid characters? After rearranging the characters for ctod, if you try variations with bad data (a very real possibility when inputting from other database tables), you should handle the errors in a proper way (i.e. null date)

e.g. This is what A5 returns for a variety of values.

?ctod("1a/2/03")
= {01/02/2003}
?ctod("a1/2/03")
= { / / }
?ctod("01/a2/03")
= {01/02/2003}
?ctod("01/2a/03")
= {01/02/2003}
?ctod("01/02/103")
= {01/02/0103}
?ctod("01/02/3")
= {01/02/2003}
?ctod("01a/02/")
= {01/02/2005}

Not always a null date for bad data, is it?

Hmmm, suppose you had your computer settings set to European style date, would your simple one work? I think (actually I know) not.

Now suppose the imported date is in a numeric format (e.g. from a spreadsheet), then can your routine recognize numeric numbers and convert them?

And believe it or not, in my opinion (although Selwyn would argue this), A5 is not Y2K compliant. If you were to enter in a birthdate of year 06, alpha 5 would say 2006, but a 2 digit birthdate always references the last 100 years prior to the present. An expiration date specified as 2 digits should always be in the future, so should be the next 100 years. Other dates should be a combination of past and future in a 100 year window. A5 does a simplistic approach, not always the best choice.

So mine doesn't care whether the input parameter is Logical, Date, Character, Blob, etc, and doesn't care which format it is. It always converts it to a date, or a null date if in error.

One call, and it always converts properly. And speed is optimized for efficient use. A5's ctod() function is fairly fast, but many of their built-in functions are not. And in a routine that has high volume usage, this does make quite a difference.

So that's the difference between a properly designed function and quick hacks of code.

It's not easy, but it makes life down the road a whole lot easier.

I'll give you another example of a function that is somewhat complex inside. I have an Append routine function that you specify the Master Table, Transaction Table and you are done. It figures out just about everything else for 99% of the appends done by people. So my call looks like this;

Append("Inventory","Inventory")

And if you add or delete a field an either table, it still works.

Change a field size or type anywhere, it still works.

Absolutely drove a client nuts when I told him that he needed no changes to his application when he changed some of his tables that were appending external data, both old and new layouts. It worked either way.

Take another common piece of code like figuring the mileage between 2 earth coordinates. Not that tough using published equations? Actually there are times when you get close to some values that the numbers go off to infinity and return totally invalid results. Making sure that doesn't happen is not particularly easy. And my version of code runs 200 times faster in XBasic than anyone else's code you might see around the message board. This probably has 3 man-weeks in the code, but it allows finding a sales office in .15 sec instead of 30 seconds while a customer is hanging on the phone.

Anyway, I hope that explains why there is hack code, good code and great code. Great code takes time and technique to produce, but it saves you in the long run. I don't expect everyone to write great code, but good code (ones that properly handles errors and runs reasonably well) should be possible by most.

I also don't expect most people to spend a whole day tinkering with DATEC() as I have, it's just not cost effective for most people's needs. But it can make programming life a whole lot easier for others. What's it worth to you? Depends, but believe me, one good function can save you ton's of time (Both programming and execution time).


Regards,
Ira

[Jerry Gray]

RE: Character to date conversion

Ira,

First you need to know that I realize your standing in the Alpha community as one of the best. I'm not trying to belittle the things that you have accomplished. I can only hope to achieve your expertise some time in the future.

I've been using Alpha for almost a year and realize everyday how much more I have to learn. In the mean time, I am trying to finish a project. When I run into a problem, I have a certain set of skills which I can apply towards solving that problem.

I agree that if you have a pet project you are going to spend a great deal of time making sure every possiblity is handled. However, if a client says he wants to convert an 8 character field in yyyymmdd format into a date format, that is what I will give him. If I approach every function with the thought that it must cover every conceivable possibility, chances are that I would never finish a project. There would probably be times when I couldn't even think of all the possibilities.

I'm sure your code is extraordinary and I congratulate you on your achievment. If the client's data source includes controls to assure date compliance, then your masterpiece and my hack will do exactly the same job.

It's a difference of meeting your needs or the client's.

Why the 2 functions? With my limited experience, I started with the first function, then tried to improve it by doing a check on the length of the string and branching accordingly. There are parts of Alpha syntax which still aren't clear to me (a lot as a matter of fact). I spent 15 minutes trying to work it out and met with failure. I did come up with 2 functions that perform exactly as they should. If you want to test the length of the string prior to calling the function that's ok too.

In Stan's original message, quote:
" would be glad to be shown to be wrong, again, and would welcome suggestions for the simplest expression which would perform the manipulations involved. I have experimented with several variations involving left(), right(), substr(), *transpose() - nested four deep with a mask, and they all seem rather ugly. "

I believe the ymdctod function (aka hack) meets Stan's need. I consider it to be properly designed as it is simple in design, performs the function properly and efficiently and meets the client's need.

Jerry Gray