none
How to check the date format mm/dd/yyyy using conditional split in ssis 2008

    Question


  • Hi,
    i have validate the date field. checking date format is mm/dd/yyyy or not.

    source date column is string data type.

    written the expression in derived column transformation and mentioned below.

    please check and verify this expression. 

    i give some input date 12-05-2012 , 15/17/2013 this data also come to output but this is incorrect format first one and 
    second is month is not in 15 so this data didnt come.

    (DT_STR,10,1252)((((DT_I4)SUBSTRING([Paid Date],1,2) <= 12) || (SUBSTRING([Paid Date],3,0) == "/") || ((DT_I4)SUBSTRING([Paid Date],4,2) <= 31) || (SUBSTRING([Paid Date],6,0) == "/") || ((DT_I4)SUBSTRING([Paid Date],7,4) >= 1900)) ? [Paid Date] : "12/31/9000") 
    result - 12/31/1900 this data only come for all records

    (DT_STR,10,1252)((((DT_I4)SUBSTRING([Paid Date],1,2) <= 12) && (SUBSTRING([Paid Date],3,0) == "/") && ((DT_I4)SUBSTRING([Paid Date],4,2) <= 31) && (SUBSTRING([Paid Date],6,0) == "/") && ((DT_I4)SUBSTRING([Paid Date],7,4) >= 1900)) ? [Paid Date] : "12/31/9000")

    result - 12/31/1900 this data only come for all records

    (DT_STR,10,1252)(((DT_I4)SUBSTRING([Paid Date],1,2) <= 12 || (DT_I4)SUBSTRING([Paid Date],4,2) <= 31 || SUBSTRING([Paid Date],3,0) == "/" || SUBSTRING([Paid Date],6,0) == "/" || (DT_I4)SUBSTRING([Paid Date],7,4) >= 1900) ? [Paid Date] : "12/31/9000")

    result - paid date record comes but invalid format and invalid record also come.

    Regards,
    Abdul Khadir.
    Friday, July 19, 2013 7:09 AM

Answers

All replies

  • Try this expression

    (( (DT_I4) SUBSTRING(createdate,1,2) <= 12) && (SUBSTRING(createdate,3,1) == "/") && ((DT_I4)SUBSTRING(createdate,4,2) <= 31) && (SUBSTRING(createdate,6,1) == "/") && ( (DT_I4) SUBSTRING(createdate,7,4) >= 1900)) ? (DT_STR,20,1252)createdate : (DT_STR,20,1252)"12/31/9000"

    Regards,

    Naveen

    Friday, July 19, 2013 8:11 AM
  • Hi Abdul,

    In order to validate the input date as mm/dd/yyyy, you can definitley write an expression but how would you make sure that input date 10/01/2013 is correct date i.e 1st Oct 2013 and not 10th Jan 2013. It may be sent as dd/mm/yyyy from source.


    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, July 19, 2013 9:31 AM
  • I would recommend rather than checking the date in mm/dd/yyyy, please check

    if the date is valid with a defined format agreed from source System Team ?,

    if yes convert the date in mm/dd/yyyy, else raise a error with invalid date.

    Agree with Nitesh, how would you make sure the source date format without knowing the format as this can cause ambiguity.

    Thanks- Prajesh Please mark the post as answered if it answers your question

    Friday, July 19, 2013 9:50 AM
  • You can also use IsDate() function for validating source data.


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    Friday, July 19, 2013 7:22 PM