Convert Number to a date

[IanWaterman]

We have a year month stored as a number eg

201112 is December 2011

I need to convert this into exactly that December 2011

In SQL I would cast as a Varchar and then use Substring to break it down. Unfortunately data is presented in a Stored Proc which I can not modify.

In Crystal I would do something similar using a totext() and mid() functions in a formula.

Is it possible to do this a an Expression, I have tried SQL syntax but I keep getting a report error.

Any pointers would be gratefully received.

I have tried to get month number

=Fields!Wording_Edition.Value - round(Fields!Wording_Edition.Value,-2)

and Monthname

=monthname(Fields!Wording_Edition.Value - round(Fields!Wording_Edition.Value,-2))

Neither of which work.

Thank you

Ian

 

[IanWaterman]

OK managed to solve this found similar function to Crystal

CStr(), Left() and Right()

But now I need to format a number into a string and retain thousand separators and lose decimals

34000000.00

Should display as numeric text 34,000,000, can this be done?

Again with Crystal I have a totext() which has optional arguments for thou separators and number of decimals. I can not find anything similar in Report Builder Functions.

Thank you

Ian

 

[IanWaterman]

Managed to find a solution to this one too

CType(Fields!Estimated_Turnover.Value, decimal).ToString("£ #,##0")

Did the trick