Converting Date String to Date

Hi there,

I am trying to convert a date string (YYYYMMDD) to a date (DDMMYY). Using the following formula, I have met with partial success, the day and the year pull through correctly for each entry but the month is always (01).

E.g. (20011031 = 31/01/2001) or (20011201 = 01/01/2001) or (20031208 = 08/01/2003)

My formula is as follows.

//Convert The Status Changed Date

NumberVar xy := tonumber(Left({AUT001.Date Status Last Changed},4));
NumberVar xd := Day(Datevalue (tonumber(Right({AUT001.Date Status Last Changed},2))+1));
NumberVar xm := Month(Datevalue (tonumber(Mid({AUT001.Date Status Last Changed},5,2))+1));

Date(xy,xm,xd)

I am using Crystal 10 and am totally stumped.

Any help would be very much appreciated, thanks in advance.Datevalue of a number ,say x, will return December (30+x), 1899.
So, according to ur formula if the month is 'May' then xm will contain January 5,1900(December (30+5+1),1899).Have added 1 additionally as u have added 1 in ur formula.
So when u take the month fn. it wil return January which is 1(as u have stated).
Hence datevalue of the number(for months at the max. can be 12) will return a date in January only and hence always u get month as January.

tried in crystal 8 and the following worked not sure about Crystal 10

numbervar xy := tonumber(left({@.stringdate},4));
numbervar xd := tonumber(right({@.stringdate},2));
numbervar xm := tonumber(Mid({@.stringdate},5,2));

Date(xy,xm,xd);|||Thank worked fine, thanks very much.

Converting Crystal Report formula to SSRS

Converting Crystal Report formula to SSRS

I've got a formula written in Crystal Reports that I'm trying to re-do in SSRS 2005. I've just been using Crystal Reports for so long, I've got a mental road-block today.

Here is the formula in Crystal Reports:
IF {V_VIEW.FIELD1} IN ["AAA", "BBB", "CCC", "DDD",
"EEE", "FFF"]
THEN ({V_VIEW.FIELD2}&"*")
ELSE ({V_VIEW.FIELD2})

(It concatonates an asterisk to the end of FIELD2 if FIELD1 contains on of the values in the list.)

In SSRS I'd like to cause an entire row to be bold if FIELD1 contains one of the values in the list.

So in SSRS I'm putting an expression into the FontWeight properties of the TableRow and trying for something (which doesn't work yet) like:
=iif (Fields!FIELD1.Value IN ("AAA", "BBB", "CCC", "DDD", "EEE", "FFF"), "BOLD", "NORMAL")

(SSRS doesn't like the "IN" in the above statement.)

Can anyone offer a suggestion on how to write this for SSRS?

Thanks!
-ErikR

UPDATE

2007-SEPT-17

Ok. I found a workable solution. Does anyone have a better suggestion than the following? The below works but it seems it could be done more simply... Any suggestions?

=iif (Fields!FIELD1.Value = "AAA","Bold",
iif(Fields!FIELD1.Value = "BBB","Bold",
iif(Fields!FIELD1.Value = "CCC","Bold",
iif(Fields!FIELD1.Value = "DDD","Bold",
iif(Fields!FIELD1.Value = "EEE","Bold",
iif(Fields!FIELD1.Value = "FFF","Bold",
iif(Fields!FIELD1.Value = "GGG","Bold",
iif(Fields!FIELD1.Value = "HHH","Bold",
iif(Fields!FIELD1.Value = "III","Bold",
iif(Fields!FIELD1.Value = "JJJ","Bold",
iif(Fields!FIELD1.Value = "KKK","Bold",
iif(Fields!FIELD1.Value = "LLL","Bold",
iif(Fields!FIELD1.Value = "MMM","Bold",
iif(Fields!FIELD1.Value = "NNN","Bold",
iif(Fields!FIELD1.Value = "OOO","Bold",
iif(Fields!FIELD1.Value = "PPP","Bold",
"Normal"))))))))))))))))

Take a look at the VB.NET Switch function for more concise syntax. Alternatively, you can code a custom function that parses the field value any way you want.

convert expression in SQL RS?

Hello:
How to convert this Crystal report formula to an
expression in SQL RS ?
if {@.Date} <> minimum({@.Date}) then
(({ByMonth.CustomerCount} - previous({ByMonth.CustomerCount})) /
previous({ByMonth.CustomerCount})) * 100
Thank you.On Aug 12, 1:40 pm, GGill <GG...@.discussions.microsoft.com> wrote:
> Hello:
> How to convert this Crystal report formula to an
> expression in SQL RS ?
> if {@.Date} <> minimum({@.Date}) then
> (({ByMonth.CustomerCount} - previous({ByMonth.CustomerCount})) /
> previous({ByMonth.CustomerCount})) * 100
> Thank you.
Assuming that @.Date is a separate dataset (sourced via query/stored
procedure) used as a hidden parameter, etc.
Something like this should work.
=iif(Parameters!Date.Value <> Min(Parameters!Date.Value), ((Fields!
CustomerCount.Value - Previous(Fields!CustomerCount.Value))/
Previous(Fields!CustomerCount.Value)) * 100, Nothing)
If this does not fit your needs, you may want to set a flag field of
some sort (in the stored procedure/query that is sourcing the report)
to check against instead of a hidden parameter, etc.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant