如何从 SQL SSIS 中的日期时间字符串中去除日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/844029/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I strip the date off of a datetime string in SQL SSIS?
提问by rrydman
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.
我正在研究一个数据仓库项目，想知道如何（最好在数据流中的派生列组件中）从 SQL 日期时间记录中去除日期片段。
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".
示例：将诸如“12/02/2008 11:32:21 AM”之类的日期时间转换为简单的“11:32:21 AM”。
回答by Michael Entin
I would just do a cast to DT_DBTIMEtype (using Derived Column transform, or Convert type transform). DT_DBTIMEcontains just (hours, minutes, seconds) part of the date/time, so you'll get rid of the date part.
我只会进行DT_DBTIME类型转换（使用派生列转换或转换类型转换）。DT_DBTIME只包含日期/时间的（小时、分钟、秒）部分，因此您将摆脱日期部分。
回答by BlackRacerBoy
Actually if you reverse the first 2 expressions like this: (DT_DBDATE)(DT_DATE)GETDATE()instead of (DT_DATE)(DT_DBDATE)GETDATE(), then you will TRUNCATEthe time off the date field.
实际上，如果您像这样反转前 2 个表达式： (DT_DBDATE)(DT_DATE)GETDATE()而不是(DT_DATE)(DT_DBDATE)GETDATE()，那么您将TRUNCATE关闭日期字段的时间。
If the DT_DATEexpression is before the DT_DBDATEexpression, you will still have the time portion in your output, but it will be reset to all zeroes.
如果DT_DATE表达式在表达式之前DT_DBDATE，您的输出中仍然会有时间部分，但它会被重置为全零。
回答by adlareg
If you need to do this in a variable expression Michael's solution won't work, but you can use the following expression:
如果您需要在变量表达式中执行此操作，Michael 的解决方案将不起作用，但您可以使用以下表达式：
(DT_DATE)(DT_DBDATE)GETDATE()
(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.
(DT_DBDATE) 仅将当前日期和时间转换为日期。但是新的数据类型与 SSIS 的日期时间不兼容。因此，您必须使用 (DT_DATE) 来转换为兼容类型。
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
此解决方案由 Russel Loski 提供，他已将其发布在他的博客中：http: //www.bidn.com/blogs/RussLoski/ssas/1458/converting-datetime-to-date-in-ssis
回答by Eric
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.
在为日程安排应用编写报告时遇到了这个问题，需要将时间存储为日期时间数据类型的一部分。我将日期时间格式化为 0，它给你这个 mon dd yyyy hh:miAM（或 PM），并且只是做了一个子字符串，它只以 AM/PM 格式返回时间。
Example below.
下面举例。
DECLARE @S DATETIME = GETDATE()
SELECT SUBSTRING(CONVERT(NVARCHAR(30), @S , 0) , 13 , 10) AS ApptTime
, CONVERT(NVARCHAR(30), @S , 0) AS ApptDate
回答by CJM
I personally use a series of functions for this. E.g.:
我个人为此使用了一系列函数。例如：
ALTER FUNCTION [dbo].[TIMEVALUE]
(
@Datetime datetime
)
RETURNS datetime
AS
BEGIN
RETURN (@Datetime - CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime))
END
I'd love to claim all the credit but it should really go to this guy.
我很想获得所有的功劳，但它真的应该归功于这个人。
