Statement Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Susan R - 25 Jan 2007 20:31 GMT

Okay I am trying to create a custom field. I have a dataset where the values
for the dates are displayed like this: 1/1/2004 is stored as 1040101, if the
date is 01/01/99, is stored as 990101.

I need to format the data to be MMDDCCYY. I am trying to use the Mid
function to extract what I need and reorg it. But the century piece? I am
trying to say if the len of the field is > 6, 20 in front of the year? Any
help would be appreciated. Thank you

=MID(Fields!LN_1ST_DISB_DATE.Value, 4, 2) &""&
MID(Fields!LN_1ST_DISB_DATE.Value, 6, 2), iif(LEN(
Fields!LN_1ST_DISB_DATE.Value)>6, "20", "19"),
&MID(Fields!LN_1ST_DISB_DATE.Value, 2, 2)

Reply to this Message

Sanjay - 26 Jan 2007 14:52 GMT

Use following:
iif(isdate(Fields!LN_1ST_DISB_DATE.Value),
format(Fields!LN_1ST_DISB_DATE.Value, "MMDDYYYY"), "00000000")

> Okay I am trying to create a custom field. I have a dataset where the values
> for the dates are displayed like this: 1/1/2004 is stored as 1040101, if the
[quoted text clipped - 9 lines]
> Fields!LN_1ST_DISB_DATE.Value)>6, "20", "19"),
> &MID(Fields!LN_1ST_DISB_DATE.Value, 2, 2)

Reply to this Message

Susan R - 26 Jan 2007 20:41 GMT

Hi,

All I am getting is 00000000 in the field now, there is no dates at all
displaying. The system I am using the report against is an AS400, if that
makes a difference?

Thanks,
Susan

> Use following:
> iif(isdate(Fields!LN_1ST_DISB_DATE.Value),
[quoted text clipped - 13 lines]
> > Fields!LN_1ST_DISB_DATE.Value)>6, "20", "19"),
> > &MID(Fields!LN_1ST_DISB_DATE.Value, 2, 2)

Reply to this Message

Sanjay - 31 Jan 2007 17:22 GMT

Hello Susan:

I am sorry I gave you wrong formula as my understanding was you have date in
MM/dd/YYYY or m/d/yy or any other such format. and you want data in MMDDYYYY
format.

As per your comments, I understand your data is in YYMMDD or YYYYMMDD format
and you want the report to show date in MM/DD/YYYY format. Is this the way
want?

If yest, you answered your question already.

use folowing:

iif (
len(data) = 8,
format(mid(data, 5, 2) + "/" + mid(data, 7, 2) + "/" + left(data, 1,
4), "MM/DD/YYYY"),
format(mid(data, 3, 2) + "/" + mid(data, 5, 2) + "/" + left(data, 1,
2), "MM/DD/YYYY")
)

This should do it.

thanks

Sanjay

MID(Fields!LN_1ST_DISB_DATE.Value, 4, 2) &""&

> > > MID(Fields!LN_1ST_DISB_DATE.Value, 6, 2), iif(LEN(
> > > Fields!LN_1ST_DISB_DATE.Value)>6, "20", "19"),
> > > &MID(Fields!LN_1ST_DISB_DATE.Value, 2, 2)

> Hi,
>
[quoted text clipped - 22 lines]
> > > Fields!LN_1ST_DISB_DATE.Value)>6, "20", "19"),
> > > &MID(Fields!LN_1ST_DISB_DATE.Value, 2, 2)

Reply to this Message