Post reply

How to check for valid dates

  • July 14, 2008 at 3:40 am

    #126450

    Hi

    I'm transferring data from an access database to an SQL table. I need to do some date checking during the transfer but I'm not sure what data flow transformation to use. I have the source and destination object setup and the mappings are correct. The check I need to do is:

    If [incident Date] >= #01/01/1753# then

    [incident Date] = [incident Date]

    Else

    [incident Date] = #01/01/1900#

    End If

    Kind Regards

    Bill Humphrey

  • July 15, 2008 at 6:27 am

    #843016

    You could use a derived column to check for this particular date.

    TransactionDate == "000000" || LEN(TRIM(TransactionDate)) == 0 ? (DT_DATE)(SUBSTRING(@[User::Default_ReportDate],5,2) + "-" + SUBSTRING(@[User::Default_ReportDate],7,2) + "-" + SUBSTRING(@[User::Default_ReportDate],1,4)) : (DT_DATE)(SUBSTRING(TransactionDate,3,2) + "-" + SUBSTRING(TransactionDate,5,2) + "-" + SUBSTRING(TransactionDate,1,2))

    This is something that I have to do in the past. Same theory should apply I believe. In this case I created a variable called @[User::Default_ReportDate] that equates to 01011900. I hope this will get you started. If not I will be happy to help more.

  • July 15, 2008 at 6:46 am

    #843040

    I will try to use your example:

    [Incident Date] >= "#07/01/1753#" ? [Incident Date] : "#01/01/1900#"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply