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
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.
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.
Hi Mona,
I tried in multiple ways including yours and able to extract the data in mm/dd/yyyy hh:mm:ss format but the thing is the column in excel is getting treated as text format instead I am looking for date format since this file gets picked up by reporting tool where it picks only date format from the excel.
I see your logic is showing 12 hour clock but I am looking for 24 hour clock for hh:mm:ss.
Here is the excel format I need for a particular column identifying it as date:
Thanks
5 people are following this question.
