SSIS - Export SQL to Excel - Date Format Issues

Question

I am new to SSIS but not SQL. I'm using SSIS to export a query to an Excel spreadsheet. The problem I'm having is the date fields are not in a date format. They appear as a date but the cell format is "General," therefore filtering a column the a date range is difficult.

I found a YouTube tutorial that helps with converting integers to currency, but I'm not having much luck with date values. I used "NumberFormat" to make the values display as a date but it's not a true date value. Below is the code that applies to the applicable column:

// for colomn L InqDate_Current 
// Set the data type of the entire column to date
Microsoft.Office.Interop.Excel.Range column_L = worksheet.Columns["L"]; 
column_L.NumberFormat = "yyyy-mm-dd";

// Get the range of the used cells in the column
Range usedRange_L = column_L.Cells.SpecialCells(XlCellType.xlCellTypeConstants); 

i = 0;
// Loop through each cell in the column
foreach (Range cell in usedRange_L)
{
    if (i != 0)
    // Convert cell value to integer and assign it back to the cell
    {
        cell.Value = cell.Value;
    }
    i++;
}

In my SSIS Package Data Flow, I have three steps:

1. Retrieve data via OLE DB Source
2. Convert Data to the proper data type (Data Conversion Transformation Editor)
3. Save data in Excel.

On Control Flow, after the Data Flow has completed, there is a step that runs a C# script that applies to specific columns to change data types.

I want the date columns to be true date columns.

Do you have any insight for me?

Thank you!

Answer 1

I figured it out after I visited How to convert any date format to yyyy-MM-dd and clicked the link to String Format for DateTime [C#].

I changed:

column_L.NumberFormat = "yyyy-mm-dd";

to

String.Format("yyyy-mm-dd", column_L);

If anyone needs more details, the YouTube video I mention with my original question is: Learn SSIS: 156 How to change excel column to numeric in ssis.

Thanks!