3

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?

Gareth
  • 5,140
  • 5
  • 42
  • 73
  • Each place you call GETDATE(), cast it as a string. – Tab Alleman Feb 11 '15 at 16:07
  • @TabAlleman `CAST` isn't a recognised function in SSIS (it's not `T-SQL`) – Gareth Feb 11 '15 at 16:12
  • Well, I didn't mean you should use the function CAST(), I meant you should CAST it as a string using the tools that are available in your environment. CAST may be a specific function in SQL, but it is also a generic programming term meaning change the datatype of something. In an SSIS expression, you perform a CAST by preceding it with the datatype in parens, like you're already doing before "DATEPART" in your first code block. – Tab Alleman Feb 11 '15 at 16:15
  • @TabAlleman Ah figured out what you mean and have got it working with `SUBSTRING((DT_WSTR, 29) GETDATE(), 1, 4)`. Thanks for your help – Gareth Feb 11 '15 at 16:19

3 Answers3

13

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
2

For my application, I needed the exact current time, and I needed it down to the second, which yielded this:

(DT_WSTR, 4)DATEPART("yyyy",GETDATE()) 
+ RIGHT("0" + (DT_WSTR, 2) DATEPART("mm",GETDATE()),2) 
+ RIGHT("0" + (DT_WSTR, 2) DATEPART("dd",GETDATE()),2)
+ RIGHT("0" + (DT_WSTR, 2) DATEPART("Hh",GETDATE()),2)
+ RIGHT("0" + (DT_WSTR, 2) DATEPART("mi",GETDATE()),2)
+ RIGHT("0" + (DT_WSTR, 2) DATEPART("s",GETDATE()),2)
David Wilson
  • 528
  • 5
  • 4
0
SUBSTRING((DT_WSTR, 29) GETDATE(), 6, 2)
Koby Douek
  • 16,156
  • 19
  • 74
  • 103
Pablo Mugica
  • 1
  • 1