SSIS 格式日期 YYYYMMDD 的日期偏移量为 X 天？
下面的评估将为我提供一个设置为 yyyymmdd 格式的日期作为 int。
我需要做的就是在 SSIS 中能够将日期偏移 X 天，并相应地保留月份和年份的偏移量。
我似乎找不到答案。每个人都有一个关于如何格式化它的示例，如下所示，但是如果您需要以天、月或年为单位的偏移量来格式化它，同时保持所有部分的准确性，该怎么办？
RIGHT((DT_STR,4,1252)YEAR(DATEADD("dd",0,getdate())),4) +""+
RIGHT("0" +(DT_STR,4,1252)MONTH(DATEADD("dd",-1,getdate())),2)+""+
RIGHT("0" +(DT_STR,4,1252)DAY(DATEADD("dd",0,getdate())) ,2)
有人吗？
在 C# 或 TSQL 中，这很容易，但这个 SSIS 正在让我失去耐心。
谢谢。
This below evaluation will give me a date set to the format yyyymmdd as an int.
What I need to do it have the ability in SSIS to offset the day by X days and keep the month and year offset accordingly.
I cant seem to find the answer. Everyone has an example on how to format it liek below, but what if you need to format it with an offset in days, months or years and at the same time keep all parts accurate.
RIGHT((DT_STR,4,1252)YEAR(DATEADD("dd",0,getdate())),4) +""+
RIGHT("0" +(DT_STR,4,1252)MONTH(DATEADD("dd",-1,getdate())),2)+""+
RIGHT("0" +(DT_STR,4,1252)DAY(DATEADD("dd",0,getdate())) ,2)
Anyone?
In C# or TSQL this is easy, but this SSIS is growing my patience thin.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是一个可能的解决方案，可以让您实现这一目标。
在 SSIS 包中，声明四个包范围变量。
TodaysDate -
DateTime类型的变量。当您设置为 DateTime 时，变量将被分配当前日期和时间。您还可以更改此设置并将其设置为您选择的日期。OffsetValue -
Int32类型的变量。这将保存偏移值。对于本示例，我选择以天为单位的偏移值。因此，我将其设置为值 7。OffsetDate -
DateTime类型的变量。选择该变量并按 F4 查看属性。将属性 EvaluateAsExpression 更改为True。将表达式设置为值DATEADD( "dd", @[User::OffsetValue] , @[User::TodaysDate] )。此表达式将偏移值添加到变量 TodaysDate，以便获得新的 OffsetDate。FormattedDate -
String类型的变量。选择该变量并按 F4 查看属性。将属性 EvaluateAsExpression 更改为True。将表达式设置为以下值RIGHT((DT_STR,4,1252)YEAR(DATEADD("dd",0, @[User::OffsetDate] )),4) +" “+RIGHT("0" +(DT_STR,4,1252)MONTH(DATEADD("dd",-1,@[用户::OffsetDate])),2)+""+
RIGHT("0" +(DT_STR,4,1252)DAY(DATEADD("dd",0,@[User::OffsetDate])) ,2)
唯一的区别是您正在格式化您的日期选择，在本例中为变量
OffsetDate中的值。该变量将保存已经偏移的日期。下面的屏幕截图显示了一个示例。变量 TodaysDate 设置为
11/28/2011。向变量添加 7 天将 OffsetDate 字段设置为12/05/2011。如您所见，变量 FormattedDate 将新的偏移日期格式化为20111205。此示例还可以通过消除变量 TodaysDate 来减少一个变量。您还可以为每种类型（例如天、月和年）设置一个偏移变量。这一切都取决于个人的喜好。
我希望这就是您正在寻找的内容，并且可能会让您了解如何实现这一目标。
Here is a possible solution that will allow you to achieve this.
In the SSIS package, declare four package scope variables.
TodaysDate - Variable of type
DateTime. When you set to DateTime, the variable will be assigned with current date and time. You can also change this and set it to the date of your choice.OffsetValue - Variable of type
Int32. This will hold the offset value. For this example, I am choosing the offset value to be in terms of days. So, I have set it to a value of 7.OffsetDate - Variable of type
DateTime. Select this variable and press F4 to view the properties. Change the property EvaluateAsExpression toTrue. Set the Expresstion to the valueDATEADD( "dd", @[User::OffsetValue] , @[User::TodaysDate] ). This expressions adds the offset value to the variable TodaysDate so you get the new OffsetDate.FormattedDate - Variable of type
String. Select this variable and press F4 to view the properties. Change the property EvaluateAsExpression toTrue. Set the Expresstion to the following valueRIGHT((DT_STR,4,1252)YEAR(DATEADD("dd",0, @[User::OffsetDate] )),4) +""+RIGHT("0" +(DT_STR,4,1252)MONTH(DATEADD("dd",-1,@[User::OffsetDate])),2)+""+
RIGHT("0" +(DT_STR,4,1252)DAY(DATEADD("dd",0,@[User::OffsetDate])) ,2)
The only difference is you are formatting the date of your choice, here in this case the value in variable
OffsetDate. This variable will hold the date that has already been offset.The below screenshot shows a sample. The variable TodaysDate is set to
11/28/2011. Adding 7 days to the variable sets the field OffsetDate to12/05/2011. As you can see, the variable FormattedDate formats the newly offset date to20111205.This example can also be done with one less variable by eliminating the variable TodaysDate. You can also have one offset variable for each type like days, months and years. It all comes down to ones' preference.
I hope this is what you were looking for and probably gives you an idea of how to achieve this.
我个人的偏好是在脚本任务中进行日期操作和格式化。 （假设您使用的是 SSIS 2008，脚本任务可以用 C# 编写。）
My personal preference is to do the date manipulation and formatting in a Script Task. (Assuming you're using SSIS 2008, Script Tasks can be written in C#.)