Home Forums SQL Server 2005 SQL Server 2005 Integration Services Need to check file name before it get process RE: Need to check file name before it get process

  • April 10, 2014 at 1:34 pm

    #1705029

    unnati.patel513 (4/10/2014)

    Hi, Thank you very much for the code

    But I need to check that if the Date is valid or not

    Lets say if we pass this SourceFile1_99999999999999.txt then should not return 1.

    We need check the condition YYYY should be between 1950 to Current year month is between 1 to 12 and day between 1 to 31

    My File format like FileName_YYYYMMDDHHMISS.txt

    I appreciate if you give me idea on that.

    Thanks,

    Unnati

    Yes, It was just a code I gave you to see how you should be writing it. see below.. feel free to add more based on that..

    DECLARE @String VARCHAR(MAX) = 'SourceFile1_20140403073209.txt'

    DECLARE @String1 VARCHAR(MAX) ='SourceFile1_2014073209.txt'

    SELECT @String, CASE WHEN CONVERT(date, LEFT(PARSENAME(REPLACE(@String, '_', '.'), 2), 8), 112) <= GETDATE() THEN 1 ELSE 0 END

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.