How to convert number to date 1

[rb2005]

I have a column which is holding data like 200601. I want to convert this like 01/06 or 01/2006 or Jan 2006 or any date format. Anyone has any clue.

 

[synapsevampire]

None of those are date formats, they are string representations of partial dates.

I'd convcert it to a date using:

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

Now you can right click the formula and format it however you like using format field->date

-k

 

[rb2005]

I guess I need totext to convert number to string then only I can use left function

 

[Madawc]

If you tried

ToText((cdate(val(left({table.field},4)),val(mid({table.field},5,2)),1)), "MMM yyyy")

you'd get Jan 2006 for 200601. Is that what you want?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[dgillz]

Multiply by 100, add 1 and you get:

20060101

Then convert to a date using the NumberToDate() function, then format as desired.

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

http://www.gainfocus.biz

 

[rb2005]

NumberToDate() is not available in Crystal.

ToText((cdate(val(left({table.field},4)),val(mid({table.field},5,2)),1)), "MMM yyyy")
also not working.

 

[Madawc]

Which version of Crystal? You must have an early one, definitely before 8.5.

'Not working' could mean several different things; please clarify.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[rb2005]

I am using 10.5
ToText((cdate(val(left({table.field},4)),val(mid({table.field},5,2)),1)), "MMM yyyy")
I am trying to convert a number to date. So left function is failing. I am getting an error.

 

[synapsevampire]

Yeah, the downside of posts such as this is that you say you have a column, not the data type...

Try:

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

-k

 

[rb2005]

thanks synapsevampire. Finally it works great.