Link to home
Start Free TrialLog in
Avatar of Elmo Erasmus
Elmo ErasmusFlag for Namibia

asked on

Change format of a date value in an SSIS variable to a text string of the format 'yyyymmdd'

I use a variable in a SQL Integration Services Package, DATEADD( "day",-365, GETDATE())  that returns a date as follows:08 Apr 2006 08:56. I want to change the value of the variable to: '20060406' as I need to use it in a SAP SQL statement. How can I do that?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

CONVERT(varchar(8), DATEADD( year,-1, GETDATE()) , 112 )  
Avatar of Elmo Erasmus

ASKER

Thanks for your response, however I get the following error message: "The function "varchar" was not recognized. Either the function name is incorrect or does not exist.

Attempt to parse the expression "CONVERT(varchar(8), DATEADD( "year",-1, GETDATE()) , 112 )" failed and returned error code 0xC004708A. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

 (Microsoft.DataTransformationServices.Controls)"

Also note that I'm using the SSIS Expression Builder to create this variable. Further help will be appreciated.
I see. for SSIS, the following should work:
REPLACE( (DT_DBDATE)DATEADD( "year",-1, GETDATE()), "-", "")

http://msdn2.microsoft.com/en-us/library/ms141704.aspx
http://msdn2.microsoft.com/en-us/library/ms141196.aspx
My initial try with your proposed solution gave the following error: "The function "REPLACE" does not support the data type "DT_DBDATE" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Evaluating function "REPLACE" failed with error code 0xC0047089."

All I want to do is to change the result of DATEADD( "day",-365, GETDATE()) which is 9 Apr 2006 08:56.  to: '20060409'.

I have tried the following:  (DT_WSTR, 30) YEAR(DATEADD( "year", -1, GetDate()))+(DT_WSTR, 30) MONTH(DATEADD( "year", -1, GetDate()))+(DT_WSTR, 30) DAY(DATEADD( "year", -1, GetDate()))
with the result: 200649. As you can see the zeros are missing.
 
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks angellll, I apprecialte your rapid and good responses