> I need to import char(10) strings that are formatted like this:
> 2007-06-14
> 2007-02-29
> 2007-06-22
> 2007-05-15
> 2007-05-22
> 1700-01-1
> Please note that one of the dates is blank, not NULL and there is one
> date not valid in SQL.
>
> I need to validate they are valid dates and insert them into a
> DateTime field which allows NULLs. If the date is invalid make it
> NULL. I also need to check for some 'known invalid dates" from our
> source system.
>
> I've been trying to use a devired column, but haven't had success yet.
> I must be missing something here or is there another way of doing
> this?
>
> In my data flow I added a deriverd column and tried using:
>
> (LEN(LTRIM((settle_cymdNew))) == 0 || settle_cymdNew == "1500-01-01"
> || settle_cymdNew == "2500-01-01" || (DT_Date)settle_ cymdNew <
> (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)settle_cymdNew
>
> Which does a good job of checking for know invalid dates, but I still
> need to check for an invalid date like 2007-02-29. I could not find
> an isDate() function for expressions.
>
> This is what I did in DTS...
>
> if ((left(strEvalDate,10) = "1500-01-01") or (left(strEvalDate,10) =
> "2500-01-01") or (left(strEvalDate,10) = "0 ") or
> len(trim(strEvalDate))=0 or (isNull(strEvalDate)) ) then
> DTSDestination(strDestName) = null
> else
> if isValidDate(strEvalDate) Then
> DTSDestination(strDestName) = strEvalDate
> else DTSDestination(strDestName) = null
> end if
> end if
> isValidDate:
>
> Function isValidDate(strEvalDate)
>
> isValidDate = False
> if not(isNull(strEvalDate)) then if ((mid(strEvalDate,5,1)="-") and
> (mid(strEvalDate,8,1)="-")) then
> if isdate(strEvalDate) = true and Cint(left(strEvalDate,4)) > 1756
> then
> isValidDate = True
> end if
> end if
> end if
> end Function

"Allan Mitchell" wrote:

> Hello appdevtech,
>
>
> What I have done before is use a Script Component as a transform and used
> Datetime.TryParseExact
>
> This then tells me if it is converible and what the datetime version is.
>
>
>
> --
>
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
> > I need to import char(10) strings that are formatted like this:
> > 2007-06-14
> > 2007-02-29
> > 2007-06-22
> > 2007-05-15
> > 2007-05-22
> > 1700-01-1
> > Please note that one of the dates is blank, not NULL and there is one
> > date not valid in SQL.
> >
> > I need to validate they are valid dates and insert them into a
> > DateTime field which allows NULLs. If the date is invalid make it
> > NULL. I also need to check for some 'known invalid dates" from our
> > source system.
> >
> > I've been trying to use a devired column, but haven't had success yet.
> > I must be missing something here or is there another way of doing
> > this?
> >
> > In my data flow I added a deriverd column and tried using:
> >
> > (LEN(LTRIM((settle_cymdNew))) == 0 || settle_cymdNew == "1500-01-01"
> > || settle_cymdNew == "2500-01-01" || (DT_Date)settle_ cymdNew <
> > (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)settle_cymdNew
> >
> > Which does a good job of checking for know invalid dates, but I still
> > need to check for an invalid date like 2007-02-29. I could not find
> > an isDate() function for expressions.
> >
> > This is what I did in DTS...
> >
> > if ((left(strEvalDate,10) = "1500-01-01") or (left(strEvalDate,10) =
> > "2500-01-01") or (left(strEvalDate,10) = "0 ") or
> > len(trim(strEvalDate))=0 or (isNull(strEvalDate)) ) then
> > DTSDestination(strDestName) = null
> > else
> > if isValidDate(strEvalDate) Then
> > DTSDestination(strDestName) = strEvalDate
> > else DTSDestination(strDestName) = null
> > end if
> > end if
> > isValidDate:
> >
> > Function isValidDate(strEvalDate)
> >
> > isValidDate = False
> > if not(isNull(strEvalDate)) then if ((mid(strEvalDate,5,1)="-") and
> > (mid(strEvalDate,8,1)="-")) then
> > if isdate(strEvalDate) = true and Cint(left(strEvalDate,4)) > 1756
> > then
> > isValidDate = True
> > end if
> > end if
> > end if
> > end Function
>
>