how to check whether in the given column we have values in date format

  • Hi,

    I have a table like the one mentioned below.

    EidVariable

    1na

    2none

    34/18/2013

    418/2013

    52013

    69999999

    I need to check for eid where variable column has data in the format mm/dd/yyyy and if suppose its not there i should neglect that particular eid.

    i shloud do this validation in SSIS.

    In informatica i can use IS_Date function but do we have any fucntions similar to is_date in SSIS?

    Please help me in this.

    Thanks in advance

    Note:Column names are just mentioned for instances

  • there is an ISDATE function in SQL as well;

    here's an example base don your post:

    /*

    /*

    Eid Variable Date? Converted?

    ---- --------- ----------- -----------------------

    1 na 0 NULL

    2 none 0 NULL

    3 4/18/2013 1 2013-04-18 00:00:00.000

    4 18/2013 0 NULL

    5 2013 1 2013-01-01 00:00:00.000

    6 9999999 0 NULL

    */

    WITH MySampleData (Eid,Variable)

    AS

    (

    SELECT '1','na' UNION ALL

    SELECT '2','none' UNION ALL

    SELECT '3','4/18/2013' UNION ALL

    SELECT '4','18/2013' UNION ALL

    SELECT '5','2013' UNION ALL

    SELECT '6','9999999'

    )

    SELECT

    MySampleData.*,

    ISDATE(Variable) AS [Date?],

    CASE

    WHEN ISDATE(Variable) = 1

    THEN CONVERT(datetime,Variable)

    ELSE NULL

    END As [Converted?]

    FROM MySampleData

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (4/17/2013)


    there is an ISDATE function in SQL as well;

    here's an example base don your post:

    /*

    /*

    Eid Variable Date? Converted?

    ---- --------- ----------- -----------------------

    1 na 0 NULL

    2 none 0 NULL

    3 4/18/2013 1 2013-04-18 00:00:00.000

    4 18/2013 0 NULL

    5 2013 1 2013-01-01 00:00:00.000

    6 9999999 0 NULL

    */

    WITH MySampleData (Eid,Variable)

    AS

    (

    SELECT '1','na' UNION ALL

    SELECT '2','none' UNION ALL

    SELECT '3','4/18/2013' UNION ALL

    SELECT '4','18/2013' UNION ALL

    SELECT '5','2013' UNION ALL

    SELECT '6','9999999'

    )

    SELECT

    MySampleData.*,

    ISDATE(Variable) AS [Date?],

    CASE

    WHEN ISDATE(Variable) = 1

    THEN CONVERT(datetime,Variable)

    ELSE NULL

    END As [Converted?]

    FROM MySampleData

    Although it's not a flaw, you've just demonstrated a "problem" with IsDate that's similar to people thinking that IsNumeric means "IsAllDigits". It'll take a whole lot more than just mm/dd/yyyy as a date. The OP's requirement is to capture only those things that look like mm/dd/yyyy as well as being a valid data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • manibad (4/17/2013)


    Hi,

    I have a table like the one mentioned below.

    EidVariable

    1na

    2none

    34/18/2013

    418/2013

    52013

    69999999

    I need to check for eid where variable column has data in the format mm/dd/yyyy and if suppose its not there i should neglect that particular eid.

    i shloud do this validation in SSIS.

    In informatica i can use IS_Date function but do we have any fucntions similar to is_date in SSIS?

    Please help me in this.

    Thanks in advance

    Note:Column names are just mentioned for instances

    I'm sure it can be done in SSIS but any "is date" function my have problems depending on what it thinks a date is. To get around that, you may have to use both ISDATE and a pattern match to guarantee the correct format.

    Borrowing heavily on Lowell's test data (which you should supply in this format in the future), here's how to do it in T-SQL. I'd do it for you in SSIS but I don't even know how to spell it correctly. 😛

    WITH MySampleData (Eid,Variable)

    AS

    (

    SELECT '1','na' UNION ALL

    SELECT '2','none' UNION ALL

    SELECT '3','4/18/2013' UNION ALL

    SELECT '4','18/2013' UNION ALL

    SELECT '5','2013' UNION ALL

    SELECT '6','9999999'

    )

    SELECT Eid,Variable

    FROM MySampleData

    WHERE 1 = CASE

    WHEN ISDATE(Variable) = 1

    AND Variable LIKE '[0-9]%/[0-9]%/[0-9][0-9][0-9][0-9]'

    THEN 1

    ELSE 0

    END

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I don't think that there is an IsDate function in SSIS. If you don't mind doing a bit of coding, you could create a script component and do a Regex test on the column - here's a link to a regular expression which validates all dates except for non-leap-year 29 Februarys.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • thanks for everyone who have replied..

    Actually i am not in a position to work on SQl or Script task and code the data to achieve my destiny..i can do it only in SSIS.Is there any possiblity to achieve it by SSIS and specfically using dervied column.

  • manibad (4/18/2013)


    thanks for everyone who have replied..

    Actually i am not in a position to work on SQl or Script task and code the data to achieve my destiny..i can do it only in SSIS.Is there any possiblity to achieve it by SSIS and specfically using dervied column.

    I mentioned a Script Component, not a Script Task.

    Regardless of that, both are 'in SSIS'.

    If you do find a derived column solution, please post back, because I'm sure it will be useful to others.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I found a method to achieve my goal in derived column in SSIS.

    the code is:

    ((SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,6,1)=="/")||

    (SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||

    (SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,4,1)=="/")||

    (SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||

    (SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,6,1)=="-")||

    (SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,5,1)=="-")||

    (SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,4,1)=="-")||

    (SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,5,1)=="-"))?

    (DT_STR,8,1252)(REPLACE((DT_WSTR,10)(DT_DBDATE)Variable,"-","")):REPLICATE(" ",8)

    but here in this code i have a glitch..

    the glitch is if suppose the data is like xx/yy/mmmm then it will throw a error..and i dunno how to rectify this glltch.for now we haven't stored such sort of data but still i want to rectify this glitch so can anyone please help me.

    Thanks in advance.

  • manibad (4/19/2013)


    I found a method to achieve my goal in derived column in SSIS.

    the code is:

    ((SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,6,1)=="/")||

    (SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||

    (SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,4,1)=="/")||

    (SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||

    (SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,6,1)=="-")||

    (SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,5,1)=="-")||

    (SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,4,1)=="-")||

    (SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,5,1)=="-"))?

    (DT_STR,8,1252)(REPLACE((DT_WSTR,10)(DT_DBDATE)Variable,"-","")):REPLICATE(" ",8)

    but here in this code i have a glitch..

    the glitch is if suppose the data is like xx/yy/mmmm then it will throw a error..and i dunno how to rectify this glltch.for now we haven't stored such sort of data but still i want to rectify this glitch so can anyone please help me.

    Thanks in advance.

    The output of the code would be

    variable=1/31/2013 o/p=20130131

    variable=31/2013 o/p=<space>

    Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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