locked
MM/DD/YYYY Format RRS feed

  • Question

  • I have Flat file as  source and sql server table as destination.

    date filed is there in my source and destination ...

    i want check the date format before going to insert the record into my destination table...

     Correct Format:  MM/DD/YYYY

    Wrong Format  :  DD/MM/YYYY or any other format.

    if i get the record MM/DD/YYYY FORMAT , record insert into destination table.

    if get record dd/mm/yyyy or any other format... record insert into FLAT FILE( Bad Records).

    how can i test the  record is  correct format or bad format....

    how can i achive this....

    please let me know..

    Monday, June 4, 2012 4:34 PM

Answers

  • You're never going to "know" that - you can only assume it based on the values you receive in the file.

    What you'll need to do is manually convert those strings into dates by specifically and intentionally converting them in a specific format.  If you leave it up to SSIS or SQL, they'll try their best to convert the dates, but may make inappropriate assumptions.  (In fact, there's absolutely no way to guarantee your code won't make mistakes either.  Tell me if 06/08/2012 is June 8th or August 6th...)

    Use a Derived Column, and the SUBSTRING function to parse that string and reformat it into the unambiguous YYYY-MM-DD format, then convert it to a date.  See more info here.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by ArthurZ Monday, June 4, 2012 5:32 PM
    • Marked as answer by Eileen Zhao Monday, June 11, 2012 6:45 AM
    Monday, June 4, 2012 5:06 PM

All replies

  • Going to be impossible to do a test like that since a lot of data that is in DD/MM/YYYY format will pass a test for MM/DD/YYYY 

    Everything up to and including the 12 day of each month would pass the test.

    Look at it this way.  What format is this date in? 02/08/2012


    Chuck Pedretti | Magenic – North Region | magenic.com



    Monday, June 4, 2012 4:40 PM
  • You're never going to "know" that - you can only assume it based on the values you receive in the file.

    What you'll need to do is manually convert those strings into dates by specifically and intentionally converting them in a specific format.  If you leave it up to SSIS or SQL, they'll try their best to convert the dates, but may make inappropriate assumptions.  (In fact, there's absolutely no way to guarantee your code won't make mistakes either.  Tell me if 06/08/2012 is June 8th or August 6th...)

    Use a Derived Column, and the SUBSTRING function to parse that string and reformat it into the unambiguous YYYY-MM-DD format, then convert it to a date.  See more info here.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by ArthurZ Monday, June 4, 2012 5:32 PM
    • Marked as answer by Eileen Zhao Monday, June 11, 2012 6:45 AM
    Monday, June 4, 2012 5:06 PM
  • we are getting date format like MM/DD/YYYy

    Tuesday, June 5, 2012 8:48 AM