在SSIS表达式中以Datestamp为零 [英] Leading Zero with Datestamp in SSIS Expression

问题描述

我想要的结果是异常 - 以YYYYMMDDHHMMSS.xls 为例，现在将是：

  \\SomePath\\ \\ Exceptions  -  20150211155745.xls 
  

我有一个问题，将前导零添加到日期和月份

我尝试转换为 DT_WSTR ，其长度设置以及选择日期分隔usg SUBSTRING ：

  @ [User :: XLFileRootDir] +DTY，（DT_WSTR，2）DATEPART（YYYY，GETDATE（））+（DT_WSTR，2）DATEPART（MM，GETDATE（））+（DT_WSTR，2） （））+.xls
  

这将导致 \\ SomePath\Exceptions - 2015211155745.xls （注意月份中缺少的前导零）。

  @[使用r :: XLFileRootDir] +异常 - +（DT_WSTR，4）SUBSTRING（GETDATE（），1，4）+.xls
  

由于函数 SUBSTRING DT_DBTIMESTAMP C $ C>。我知道某些转换需要进行，但是无法在SSIS中找到一个功能来完成此操作。

任何人都可以帮助我如何格式化表达式带有前导零？

解决方案

您遇到的问题是YEAR / MONTH / DAY函数返回一个整数。整数不会显示前导零。因此，窍门是将其转换为字符串。为该字符串预设前导零。然后，使用 RIGHT 函数剪除最后2个字符。修剪仅在10月，11月和12月需要，但逻辑更为清晰，无条件地应用 RIGHT 。

这将构建您的YYYYMMDD字符串。

 （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'm trying to format a datestamp to have leading zeros in an expression using SSIS 2008 R2.

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

\\SomePath\Exceptions - 20150211155745.xls

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

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"

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

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

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?

解决方案

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.

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)

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表达式中以Datestamp为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！