Hi @Tadishetty, Sandeep ,
MS Excel has no notion of data types. It a virtual electronic "paper" that accepts any scribbles.
Each Excel cell has two things:
- raw data
- formatting
So you need to keep raw data in SSIS as DATETIME format yyyy-mm-dd hh:mm:ss based on ISO 8601 standard. After that switch to Excel and format relevant cells column via a custom format for human eyes.
What will it do? Exactly as I explained above. Excel will keep raw data as is, and custom formatting will present it as asked by a custom formatting. Such setup will allow date and time operations in Excel.
Please see below how it looks in Excel.