Date Conversion 1

[genekr]

I have a numeric field used for the date on a Pervasive SQL Server v9. The field is numeric and is format 20060101. I need to convert this to a mm/dd/yyyy field to use in Crystal Reports 9. Can this be done in Crystal?

Gene

 

[synapsevampire]

Try a formula of:

cdate(val(left{table.field},4)),val(mid({table.field},5,2)),val(mid({table.field},7,2)))

-k

 

[genekr]

When I enter this formula
cdate(val(left{ARCUSFIL.CUS_LAST_SALE_DT},4)),val(mid({ARCUSFIL.CUS_LAST_SALE_DT},5,2)),val(mid({ARCUSFIL.CUS_LAST_SALE_DT},7,2))I get an error that a ) is missing at the end of the formula. If I enter a ), it tells me there is another ) missing after the last ) I just put in.

 

[synapsevampire]

Sorry, I did it for a string, use:

whileprintingrecords;
stringvar MyDate:=totext({table.field,0,"");
cdate(val(left(MyDate,4)),val(mid(MyDate,5,2)),val(mid(MyDate,7,2)))

-k

 

[dgillz]

NumbertoDate() was specifically created to do this and should be available in Crystal 9. In prior versions of Crystal this was a UFL.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[genekr]

Thank you so much.

 

[mocgp]

dgillz - I am curious about your reference to the NumberToDate() function in Crystal. It's not in version 10 and I can't locate any documentation about it. Could you provide more details please?

 

[dgillz]

Look under additional functions under the tdate tree. This is not listed as a date function.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[mocgp]

I have no "tdate" tree under Additional Functions. I think Oracle 9i probably doesn't provide it.