T-SQL SSIS Export DateTime type to Date Time-CodePudding

I have some columns in Sql Server in DateTime type column and need to export them using SSIS to csv in Time format and Date format. I can use format and cast to get them to display as time and date in a query as listed to the right part of the results in the columns Start Time, End Time, tart Date, End Date.

  ,BEGIN_TIM [Start DTTime]  --datetime
  ,END_TIM [End DTTime]  --datetime
  ,BEGIN_DTE [Start DTDate] --datetime
  ,END_DTE [End DTDate]  --datetime
  
  ,FORMAT(BEGIN_TIM, 'hh:mm tt' ) [Start Time]
  ,FORMAT(END_TIM, 'hh:mm tt' ) [End Time]

  ,cast(BEGIN_DTE as date) [Start Date] 
  ,cast(END_DTE as date) [End Date]

Results

-- Start DTTime             End DTTime              Start DTDate            End DTDate              Start Time  End Time    Start Date  End Date
--1900-01-01 08:30:00.000   1900-01-01 12:30:00.000 2022-05-10 00:00:00.000 2022-06-28 00:00:00.000 08:30 AM    12:30 PM    2022-05-10  2022-06-28
--1900-01-01 08:30:00.000   1900-01-01 12:30:00.000 2022-07-07 00:00:00.000 2022-08-09 00:00:00.000 08:30 AM    12:30 PM    2022-07-07  2022-08-09
--1900-01-01 08:30:00.000   1900-01-01 12:30:00.000 2022-08-03 00:00:00.000 2022-08-12 00:00:00.000 08:30 AM    12:30 PM    2022-08-03  2022-08-12
--1900-01-01 00:00:00.000   1900-01-01 12:00:00.000 2022-08-02 00:00:00.000 2022-08-12 00:00:00.000 12:00 AM    12:00 PM    2022-08-02  2022-08-12

I insert those columns to a table and then in SSIS connect the OLE DB Source to a View. When I export the columns with the Flat File Connection Manager they come out in datetime format.

Flat File Connection Manager Exports Datetime format

"Start time"         ,"End Time"           ,"Start Date"         ,"End Date"           
"1900-01-01 08:30:00","1900-01-01 12:30:00","2022-05-10 00:00:00","2022-06-28 00:00:00"

If I set the Data types in Flat File Connection Manager Advanced to DT_DBTME DT_DBDATE it doesn't save when I close and then view the Data type again.

It looks like I might need to use the Time data type and Date data type in Sql Server or find a way to format the export as Time and Date in SSIS. That might mean using a derived column.

What's the best way to export datetime type columns to a csv file in Time column and Date columns?

One solution- If I format the columns in the View that is used for the Ole DB Source SSIS sets the Data Type to Unicode String DT_WSTR and the columns export in the correct format.

        FORMAT([Start Time], 'hh:mm tt' ) [Start Time], FORMAT([End Time], 'hh:mm tt' ) [End Time], 
        FORMAT([Start Date], 'dd-MM-yyyy' ) [Start Date], FORMAT([End Date], 'dd-MM-yyyy' ) [End Date], 

"Start time","End Time","Start Date","End Date" 
"08:30 AM"  ,"12:30 PM","10-05-2022","28-06-2022"

CodePudding user response：

It appears you need dates and times exported in a particular format. While you're coercing them to the correct representation in your select statement, it is crucial that the metadata the pipeline receives is that the data is typed as a string DT_STR/DT_WSTR.

If the first time those columns were presented to the pipeline they were a date or time type, then SSIS may have kept the metadata and is implicitly converting back to a datetime type. Double clicking the pipeline between the source and destination will show what the types are.

The same holds true for your Flat File Format connection manager. Here, you want to have the date and time columns specified as string because while it contains date and time data, you have a specific format in mind so leave it as a string of the correct length (and unicode-ness).