Date Conversion

I am having trouble with a simple date conversion in a table. I have
a field that is stored in a legacy system as a text value. When I
bring the data into reporting services I need to convert it to a date,
however, some records have invalid date formats, which cannot be
converted. I tried to solve the problem using the following
expression, but I still get the dreaded "#Error" in the fields that
cannot be converted to date.
=IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
ABACCD.Value),nothing)Perhaps you could use regular expressions such as:
System.Text.RegularExpressions.Regex.IsMatch(StartDate,
"(((0[1-9]|[1-9])|1[012])[- /.]((0[1-9]|[1-9])|[12][0-9]|3[01])[-
/.](19|20)\d\d)|((January|February|March|April|May|June|July|August|September|October|November|December)(\s)((0[1-9]|[1-9])|[12][0-9]|3[01])(,\s)(19|20)\d\d)|((Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\s)((0[1-9]|[1-9])|[12][0-9]|3[01])(,\s)(19|20)\d\d)")
This returns false if the format is incorrect
"repettry@.fcbinc.com" wrote:
> I am having trouble with a simple date conversion in a table. I have
> a field that is stored in a legacy system as a text value. When I
> bring the data into reporting services I need to convert it to a date,
> however, some records have invalid date formats, which cannot be
> converted. I tried to solve the problem using the following
> expression, but I still get the dreaded "#Error" in the fields that
> cannot be converted to date.
> =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> ABACCD.Value),nothing)
>|||On Apr 11, 10:32 pm, repet...@.fcbinc.com wrote:
> I am having trouble with a simple date conversion in a table. I have
> a field that is stored in a legacy system as a text value. When I
> bring the data into reporting services I need to convert it to a date,
> however, some records have invalid date formats, which cannot be
> converted. I tried to solve the problem using the following
> expression, but I still get the dreaded "#Error" in the fields that
> cannot be converted to date.
> =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> ABACCD.Value),nothing)
You're probably going to need to use custom code to do what you're
attempting to do with the IIF. Since IIF is a function call, the
entire statement is evaluated at run time so it's throwing the error
simply because it's seeing the invalid date value in the statement.
Something like this should work:
Public function ConvertDate (exp1)
If IsDate(exp1) = False Then
ConvertDate = Nothing
Else ConvertDate = CDate(exp1)
End If
End function
Then insert
=code.ConvertDate(Fields!AVACCD.Value)
in the appropriate text box.|||On Apr 13, 1:53 pm, "toolman" <t...@.infocision.com> wrote:
> On Apr 11, 10:32 pm, repet...@.fcbinc.com wrote:
> > I am having trouble with a simple date conversion in a table. I have
> > a field that is stored in a legacy system as a text value. When I
> > bring the data into reporting services I need to convert it to a date,
> > however, some records have invalid date formats, which cannot be
> > converted. I tried to solve the problem using the following
> > expression, but I still get the dreaded "#Error" in the fields that
> > cannot be converted to date.
> > =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> > ABACCD.Value),nothing)
> You're probably going to need to use custom code to do what you're
> attempting to do with the IIF. Since IIF is a function call, the
> entire statement is evaluated at run time so it's throwing the error
> simply because it's seeing the invalid date value in the statement.
> Something like this should work:
> Public function ConvertDate (exp1)
> If IsDate(exp1) = False Then
> ConvertDate = Nothing
> Else ConvertDate = CDate(exp1)
> End If
> End function
> Then insert
> =code.ConvertDate(Fields!AVACCD.Value)
> in the appropriate text box.
Thanks. The custom code worked great! I used the same concept to
convert numbers as well.