SSIS 表达式中带日期戳的前导零 [英] Leading Zero with Datestamp in SSIS Expression

问题描述

我正在尝试使用 SSIS 2008 R2 将日期戳格式化为在表达式中包含前导零.

I'm trying to format a datestamp to have leading zeros in an expression using SSIS 2008 R2.

我想要的结果是 Exceptions - YYYYMMDDHHMMSS.xls 举个例子，现在是:

My desired result would be Exceptions - YYYYMMDDHHMMSS.xls so as an example, now would be:

\SomePathExceptions - 20150211155745.xls

我在向日和月添加前导零时遇到问题.

I am having an issue adding the leading zeros to the day and month though.

我尝试了以下表达式，尝试将长度设置为 DT_WSTR 并选择将日期分开 usg SUBSTRING:

I've tried the following expressions by trying to convert to DT_WSTR with the length set as well as picking the date apart usg SUBSTRING:

@[User::XLFileRootDir] + "Exceptions - " + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 2) DATEPART("DD", GETDATE())  + ".xls"

这会导致 \SomePathExceptions - 2015211155745.xls(注意月份中缺少的前导零).

This results in \SomePathExceptions - 2015211155745.xls (notice the missing leading zero on the month).

@[User::XLFileRootDir] + "Exceptions - " + (DT_WSTR, 4) SUBSTRING(GETDATE(), 1, 4) + ".xls"

这会导致错误，因为函数 SUBSTRING 不支持数据类型 DT_DBTIMESTAMP.我知道需要进行某种转换，但在 SSIS 中找不到完成此操作的函数.

This results in an error as the data type DT_DBTIMESTAMP isn't supported by the function SUBSTRING. I'm aware that some sort of conversion needs to take place but can't find a function within SSIS to complete this.

谁能帮我设置带前导零的表达式的格式?

Could anyone help me with how to format the expression with leading zeros?

推荐答案

您遇到的问题是 YEAR/MONTH/DAY 函数返回一个整数.整数不会出现前导零.因此，技巧"是将其转换为字符串.在该字符串前添加一个前导零.然后，使用 RIGHT 函数剪掉最后 2 个字符.仅在 10 月、11 月和 12 月需要修剪，但无条件应用 RIGHT 的逻辑更清晰.

The problem you're running into is that the YEAR/MONTH/DAY functions return an integer. An integer won't present leading zeros. Therefore, the "trick" is to convert it to a string. Prepend a leading zero to that string. Then, shear off the last 2 characters using the RIGHT function. The trimming is only required for October, November, and December but the logic is cleaner to unconditionally apply RIGHT.

这会构建您的 YYYYMMDD 字符串.

This builds your YYYYMMDD string.

(DT_WSTR, 4)YEAR(@[System::StartTime]) 
+ RIGHT("0" + (DT_WSTR, 2) MONTH(@[System::StartTime]), 2) 
+ RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)

我发现使用变量 System::StartTime 而不是 GETDATE() 更好，尤其是在涉及时间时.每次检查时都会评估 GETDATE.在长时间运行的包中，返回的值可能会有相当大的漂移.System::StartTime 是包本身开始的时间.它对于运行本身来说是恒定的，但显然会重置每次运行.

I find it better to use the variable System::StartTime rather than GETDATE(), especially when time is involved. GETDATE will be evaluated each time it is inspected. Over long running packages, there can be a sizable drift in the values returned. System::StartTime is the time the package itself began. It is constant for the run itself but obviously resets per run.

这篇关于SSIS 表达式中带日期戳的前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！