如何在 SQL SSIS 中从日期时间字符串中删除日期?
我正在开发一个数据仓库项目,并且想知道如何(最好在数据流中的派生列组件中)从 SQL 日期时间记录中剥离日期片段。
将日期时间转换为时间后,我将查找时间以在时间维度表中查找相关时间记录。
有人可以给我一个简单的函数来在派生列转换中完成此操作吗?
示例:将“12/02/2008 11:32:21 AM”等日期时间简单地转换为“11:32:21 AM”。
I'm working on a data warehouse project and would like to know how to (preferably in a Derived Column component in a Data flow) strip the date piece off of a SQL datetime record.
Once I have the datetime converted to just a time I am going to do a lookup on the time to find the related time record in a time dimension table.
Can someone give me a simple function to accomplish this inside a derived column transform?
Example: Transform a datetime such as "12/02/2008 11:32:21 AM" into simply "11:32:21 AM".
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
我只需转换为 DT_DBTIME 类型(使用派生列转换或转换类型转换)。
DT_DBTIME
仅包含日期/时间的(小时、分钟、秒)部分,因此您将删除日期部分。I would just do a cast to
DT_DBTIME
type (using Derived Column transform, or Convert type transform).DT_DBTIME
contains just (hours, minutes, seconds) part of the date/time, so you'll get rid of the date part.如果您需要在变量表达式中执行此操作,则迈克尔的解决方案将不起作用,但您可以使用以下表达式:
(DT_DBDATE) 仅将当前日期和时间转换为日期。 但新的数据类型与 SSIS 的日期时间不兼容。 因此,您必须使用 (DT_DATE) 来转换为兼容类型。
该解决方案由 Russel Loski 提供,他已将其发布在他的博客中:
http://www.bidn.com /blogs/RussLoski/ssas/1458/converting-datetime-to-date-in-ssis
If you need to do this in a variable expression Michael's solution won't work, but you can use the following expression:
(DT_DBDATE) converts the current date and time to a date only. But the new datatype is not compatiple with SSIS's datetime. Therefore you'll have to use (DT_DATE) for converting to a compatible type.
Courtesy of this solution belongs to Russel Loski who has posted it in his blog:
http://www.bidn.com/blogs/RussLoski/ssas/1458/converting-datetime-to-date-in-ssis
实际上,如果你像这样反转前两个表达式:
(DT_DBDATE)(DT_DATE)GETDATE()
而不是
(DT_DATE)(DT_DBDATE)GETDATE()
,那么您将TRUNCATE
日期字段中的时间。如果
DT_DATE
表达式位于DT_DBDATE
表达式之前,您的输出中仍将包含时间部分,但它将重置为全零。Actually if you reverse the first 2 expressions like this:
(DT_DBDATE)(DT_DATE)GETDATE()
instead of
(DT_DATE)(DT_DBDATE)GETDATE()
, then you willTRUNCATE
the time off the date field.If the
DT_DATE
expression is before theDT_DBDATE
expression, you will still have the time portion in your output, but it will be reset to all zeroes.在为调度应用程序编写报告时遇到了这个问题,需要将时间存储为日期时间数据类型的一部分。 我将日期时间格式化为 0,它为您提供了 mon dd yyyy hh:miAM(或 PM),并且仅执行了仅以 AM/PM 格式返回时间的子字符串。
下面的例子。
Ran into this with writing a report for a scheduling app, needed the time that was stored as part of a datetime data type. I formated the datetime as 0 which gives you this mon dd yyyy hh:miAM (or PM), and just did a substring of that which returned the time only in an AM/PM format.
Example below.
我个人为此使用了一系列函数。 例如:
我很想获得所有的功劳,但它确实应该归于 这个人。
I personally use a series of functions for this. E.g.:
I'd love to claim all the credit but it should really go to this guy.