none
Data type Conversion RRS feed

  • Question

  • Hi All,

              Table1                                           Table2
     Col.                    Datatype        |    Col                    Datatype
    Begindate              Varchar        |  PeriodDate          smalldate
      20091103                                  6/19/2006 12:00:00 AM
     
    In this I want to match the table 1 col.(Begindate) with Tbale 2 col(PeriodDate)

    Am doing like this:

    taking a DerivedCol. editor and Putting (DT_DBTIMESTAMP)(SUBSTRING(BeginClassDate,1,4) + "-" + SUBSTRING(BeginClassDate,5,2) + "-" + SUBSTRING(BeginClassDate,7,2)) adding a new col.

    Again am taking Derived Col editor and Adding new col.
     
    DATEADD("dd",-30,Begindate) <= Begindate && DATEADD("dd",30,Begindate) >= Begindate.

    am taking the Datatype DateTimeStamp.
     But during Matching its saying Datatype is Invalid How we can do that ..

    Quick Answer Appricated.

    Thanks






    Wednesday, December 9, 2009 4:17 PM

Answers

  • The expression would check if the LoanPeriodDate is greater than a date and less than date which eventually will be either true or false (boolean). Why are you using this in a derived column? If your reuirement is to take forward only those rows where LoanPeriodDate is withiin a particluar range, then you can use a conditioanl split to filter the rows. If you want to assign a value to some other column based on the decision wether LoanPeriodDate falls within a particular range or not then you can use Derived Column in this way:
    Deived Column Name                            Expression
    --------------------------------------------------------------------------
    NewColName           <LoanPeriodDate Check Expression>? "Value if check expression is TRUE" : "Else Value"

    I think, the expression you have put for checking the LoanPeriodDate will always be true as you are comparing LoanPerioDate against LoanPeriodDate using DATEADD function.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by adurs Thursday, December 10, 2009 3:02 AM
    Thursday, December 10, 2009 12:03 AM

All replies

  • Your period date is like mm/dd/yyyy and the new column inside derived column is yyyy--mm-dd. Try to convert Begin Date in same format as of the Period date like:
    (DT_DBTIMESTAMP)(SUBSTRING(BeginClassDate,5,2) + "/" + SUBSTRING(BeginClassDate,7,2) + "/" + SUBSTRING(BeginClassDate,1,4))


    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, December 9, 2009 4:31 PM
  • I have taken the same expression which u said

    (DT_DBTIMESTAMP)(SUBSTRING(BeginDate,5,2) + "/" + SUBSTRING(BeginDate,7,2) + "/" + SUBSTRING(BeginDate,1,4))   and the dattype is Datetime stamp  but still not matching


    showing the datatype  error during matching.
    Wednesday, December 9, 2009 4:37 PM
  • Okay..what is the data type of both the columns? Just put the mouse cursor on the column name inside the lookup editor and observe the datatype of both the columns.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Edited by Nitesh Rai Thursday, December 10, 2009 5:42 AM
    Wednesday, December 9, 2009 4:43 PM
  • It had matched now, but do we need to take the datatype as boolean in new col. where we gave the expression DATEADD("dd",-30,LoanPeriodDate) <= LoanPeriodDate && DATEADD("dd",30,LoanPeriodDate) >= LoanPeriodDate. 

    (DT_DBTIMESTAMP)(SUBSTRING(BeginClassDate,5,2) + "/" + SUBSTRING(BeginClassDate,7,2) + "/" + SUBSTRING(BeginClassDate,1,4))        taking datatype as Databasetime Stamp.

    wat wuld be the datatype for  DATEADD("dd",-30,LoanPeriodDate) <= LoanPeriodDate && DATEADD("dd",30,LoanPeriodDate) >= LoanPeriodDate. we are taking Boolean.  wat you think...

    Its matching now for boolean...but is this write or wrong...I not sure...

    thanks  

     
    Wednesday, December 9, 2009 4:51 PM
  • Since you are using the exprssion inside a conditionl split, it will be boolean. Depending on the boolean value true or false you will get the output from conditional split
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, December 9, 2009 4:53 PM
  • Srry ..for late reply....am not taking that in expression in Conditional split....am taking 2 derived cols...and placing each of the expression ....in each derived col....
    Wednesday, December 9, 2009 6:00 PM
  • What is the complete expression you are using inside derived column?


    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, December 9, 2009 7:28 PM
  • DATEADD("dd",-30,LoanPeriodDate) <= LoanPeriodDate && DATEADD("dd",30,LoanPeriodDate) >= LoanPeriodDate
    Wednesday, December 9, 2009 8:13 PM
  • The expression would check if the LoanPeriodDate is greater than a date and less than date which eventually will be either true or false (boolean). Why are you using this in a derived column? If your reuirement is to take forward only those rows where LoanPeriodDate is withiin a particluar range, then you can use a conditioanl split to filter the rows. If you want to assign a value to some other column based on the decision wether LoanPeriodDate falls within a particular range or not then you can use Derived Column in this way:
    Deived Column Name                            Expression
    --------------------------------------------------------------------------
    NewColName           <LoanPeriodDate Check Expression>? "Value if check expression is TRUE" : "Else Value"

    I think, the expression you have put for checking the LoanPeriodDate will always be true as you are comparing LoanPerioDate against LoanPeriodDate using DATEADD function.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by adurs Thursday, December 10, 2009 3:02 AM
    Thursday, December 10, 2009 12:03 AM
  • Thanks Nitesh...for your solution...
    Thursday, December 10, 2009 3:03 AM