String Conversion 2

[mnmdesigns]

Hi,

I am using CR 8.5 with a SQL server 2000 backend database. I have a string field which has data like {YYYYMMDD}. I am trying to convert it into
{MM/DD/YY}

Please note that I need to add the "/" and then only show "YY" instead of "YYYY".

Thanks in advance for your help.
MD

 

[dgillz]

Use the NumberToDate() function:

NumberToDate({YourField})

You may need to download the NumberToDate() function from the crystal decisions website. This functino does not normally ship with Crystal but the UFL is there to download.


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

http://www.trianglepartners.com

 

[synapsevampire]

Use a SQL Expression to convert the date on the database side, it simplifies everything.

Or in Crystal use a formula:

cdate(val(mid({table.date},3,2)),val(mid({table.date},5,2)),val(mid({table.date},7,2))

Not that it's a date you can format it however you'd like by right clicking it and selecting format field, plus you can treat it like a real date (comparative, grouping, sorting, etc.)

-k

 

[nagornyi]

StringVar a := {Your.Field};
NumberVar yr := ToNumber(a[1]+a[2]+a[3]+a[4]);
NumberVar mn := ToNumber(a[5]+a[6]);
NumberVar dy := ToNumber(a[7]+a[8]);
Date(yr, mn, dy)

 

[Reebo99]

Lot's of ways to do this one.....

Date(Picture("20031031","xxxx/xx/xx&quot)

replace the item in bold with your field......

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[mnmdesigns]

Thanks for all your help. I have tried implementing a couple of the solutions provided above.

Using synaspevampire's formula :

I am getting 8/3/4
as compared to
08/03/04

Can some please tell me how to fix this?

and using nagorny's solution.

I am getting 8/3/2004.

Can someone please help!

Thanks,
MD.

 

[Reebo99]

Did you try my solution?

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[mnmdesigns]

Reebo,

Yes I tried your solution and it looked like this

Date(Picture("{participant.qualify_date}","xxxx/xx/xx&quot)

One of the things is that I need only last 2 digits of the year. The end result should look like

MM/DD/YY
08/01/03

Was I supposed to put the field name or the data from the field in your report?

Thanks for your help.
MD

 

[synapsevampire]

Right click any field once it's a date (such as the formula I'd supplied) and select format field.

You can format it however you'd like once it's a date.

-k

 

[Reebo99]

Also, you can force the format within the formula by converting it back to text :

ToText(Date(Picture({participant.qualify_date},"xxxx/xx/xx&quot),"MM/dd/yy&quot



Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[kchaudhry]

Thanks alot guys. It is working fine now.

You guyz are the best!!

MD

Kchaudhry

 

[mnmdesigns]

Sorry .. was working off my co workers computer.

Thanks for your help.

MD.