CODEWITHSUNDEEP
sql-serverssisexpressionsubstring
Gareth
Gareth

Reputation: 5243

Leading Zero with Datestamp in SSIS Expression

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?

Upvotes: 3

Views: 11055

Answers (3)

David Wilson
David Wilson

Reputation: 548

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)

Upvotes: 2

Pablo Mugica
Pablo Mugica

Reputation: 1

SUBSTRING((DT_WSTR, 29) GETDATE(), 6, 2)

Upvotes: 0

billinkc
billinkc

Reputation: 61211

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.

Upvotes: 13

Related Questions