Date is a numeric field, convert to date format

[Varco]

I have a situation using V7 of Crystal that I need to display dates in the report, however they are stored in the DB as a numeric field using the format 20010702.00 to represent July 2, 2001. I have worked out several different ways to convert this value and use it with the date command, most of which convert it to a string and then extract the different portions for year, month and day. I just want to be sure that Crystal does not have a function that use this field format for dates without any prior manipulation. Jim Varco
Varco Consulting
jimvarco@worldnet.att.net

 

[FoxG]

Hi,

This URL has been suggested in earlier messages.

This may help.

http://fp.chaseinternational.f9.co.uk/download.htm

 

[Varco]

Thanks for the tip on the URL. Checked it out, DL'd the file and it does most of what I need. The dates I am dealing with are stored as a 4 character year, this function expects the year as 2 characters. It is still useful to me, I will just have to do some work on the field first to strip the first two characters from the year. Thanks again.

Jim Varco
Varco Consulting
jimvarco@worldnet.att.net

 

[FoxG]

Hi,

Watch out for Y2K problems if using 2 character years

I would have thought using

date(tonumber(left(totext({field}),4),
tonumber(mid(totext({field}),4,2),
tonumber(mid(totext({field}),6,2))

is fine for what you need

Geoff

 

[Varco]

Geoff,

The formula you listed is almost exactly what I am using, also a similar one to convert date parameter entries for use with the numeric date field. Correct me if I am wrong, shouldn't the MID function for the month start at position 5 and for the day at position 7? I also had to use the ToText option of "" to strip out the commas, or allow for them in the positioning of the characters if I did not use it. (Forgot to add the commas to my original post).

I just wanted to be sure that I had not missed a Crystal function that would handle the field without manipulating it. Thanks for the help and interest. Jim Varco
Varco Consulting
jimvarco@worldnet.att.net

 

[FoxG]

Hi,

Well spotted, just checking you were paying attention !!

Geoff

 

[Varco]

Geoff,

Thanks again!

Jim Varco
Varco Consulting
jimvarco@att.net

 

[dgillz]

Varco,

It sounds like you have a solution, but I am a Macola COnsultasnt and macola stores its dates as a number YYYYMMDD. I have a UFL - User Function Library called NumbertoDate which converts an eight digit number to a date. If you are interested let me know and I'll email it to you. Dgilsdorf@altavista.com Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[Ngolem]

hmmmm....if it is different from that described by FoxG why not post it so all can benefit??

jim

 

[dgillz]

NGolem,

Tell me how to post a file on here and I'll be glad to do it. Otherwise email me and I'll gladly forward the UFL to you. Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Dgilsdorf#altavista.com
714-348-0964

 

[Ngolem]

Just copy and paste the contents

 

[dgillz]

NGolem,

I have a file called U2fldate.dll which contains the number to date UFL. I would be glad to copy the contects here, but I do not know how to display the contents, do you?
If so please let me know.

I still don't know how to post a file on here. Is there a way to do that? Again, I do not have a URL where the file can be downloaded, just the file itself.

Any ideas?

Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Dgilsdorf#altavista.com
714-348-0964