mm-dd-yyyy hh:mm:ss in excel destination using ssis

Question

Hi,

I am trying to extract the data from SQL table with datetime as datatype to excel sheet and I need the data in excel sheet as mm/dd/yyyy hh:mm:ss. Please let me know the solution since I am getting text format in excel column as yyyy-mm-dd hh:mm:ss instead of mm/dd/yyyy hh:mm:ss format.

SQL Source:

Data I need in Excel Destination:

I extracted data but got the wrong format in excel sheet:

Thanks,
Sandeep

Answer 1

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.

Answer 2

Hi @Tadishetty, Sandeep ,

We can use Data Conversion and Derived Column in ssis Data Flow Task.

Please refer to the following pictures:

1. 
2. 
   TOKEN((DT_WSTR,50)Date1," ",1) + " " + TOKEN((DT_WSTR,50)Date1," ",2)
3. 

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.