T-SQL SSIS导出DateTime类型到日期时间
我在DateTime类型列中的SQL Server中有一些列，需要在时间格式和日期格式中使用SSIS将其导出。我可以使用格式和铸造来使它们显示为在列中列出的结果中列出的查询中的时间和日期。
,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]
结果
-- 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
我将这些列插入表格，然后在SSIS中将OLE DB源连接到视图。当我用平面文件连接管理器导出列时，它们以DateTime格式出现。
Flat File Connection Manager导出DateTime格式，
"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"
如果我将数据类型设置为Flat File Connection Manager中的DT_DBTME DT_DBDATE ADVANCE ADVANCE ADVANCE ADVAND MALERAT，当我关闭时，它不会保存，然后再次查看数据类型。
看来我可能需要使用SQL Server中的时间数据类型和日期数据类型，或找到将导出格式化为SSIS中的时间和日期的方法。这可能意味着使用派生列。
在时间列和日期列中将DateTime类型列导出到CSV文件的最佳方法是什么？
一个解决方案 - 如果我将用于OLE DB源的视图中的列格式化，则SSIS将数据类型设置为Unicode String dt_wstr，并以正确格式导出列。
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"
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"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
看来您需要以特定格式导出的日期和时间。当您将它们胁迫为选择语句中的正确表示时，至关重要的是，管道收到的元数据是将数据作为字符串dt_str/dt_wstr键入。
如果第一次将这些列出现到管道上，它们是日期或时间类型，则SSIS可能保留了元数据，并隐式转换回DateTime类型。双击源和目标之间的管道将显示什么是类型。
平面文件格式连接管理器也是如此。在这里，您需要将日期和时间列指定为字符串，因为它包含日期和时间数据时，您会牢记特定的格式，因此将其作为正确的长度（和Unicode-ness）的字符串。
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).