question

TadishettySandeep-8327 avatar image
0 Votes"
TadishettySandeep-8327 asked ·

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

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:

75604-image.png



Data I need in Excel Destination:

75615-image.png


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

75605-image.png

Thanks,
Sandeep


sql-server-integration-services
image.png (9.4 KiB)
image.png (9.1 KiB)
image.png (8.5 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

Hi @TadishettySandeep-8327,

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.

75874-excel-cell-formatting.png


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks YitzhakKhabinsky.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @TadishettySandeep-8327 ,

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

Please refer to the following pictures:
1. 75672-dataconversion.png
2. 75608-derivedcolumn.png
TOKEN((DT_WSTR,50)Date1," ",1) + " " + TOKEN((DT_WSTR,50)Date1," ",2)
3. 75681-exceldata.png

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.




dataconversion.png (13.5 KiB)
derivedcolumn.png (17.8 KiB)
exceldata.png (9.4 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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:


75649-image.png

Thanks


0 Votes 0 ·
image.png (60.5 KiB)