marvo2010
asked on
Converting DT_WSTR to DT_DBDATE in for loop container
Hello ,
I am incrementing a date value (string typed)in the for each loop container.
using @NewDate = DATEADD("day",1,@NewDate)
It generates the error :
DateADD does not support the data type "DT_WSTR" for parameter number 3. The type of the parameter could not be implicity cast into a compatible type for the function .To perform this operation , the operands needs to be explicity cast with a cast operator.
So I had to cast it using :
@NewDate= DATEADD("day",1,(DT_DBDATE
And then I get : Error code 0x80020005 occured attempting to convert from data type DT_WSTR to data typr DT_DBDATE
which seems to suggest that I cant do the conversion,
does any one know if this conversion is possible and how I can do it. Obviously the @NewDate is assigned string type because thats what is available in control flow for variables for date of specific value . The datetime type in the variables window defaults to getdate() value .
I have derived the initial value of @NewDate fron an execute SQL task
Thanks
I am incrementing a date value (string typed)in the for each loop container.
using @NewDate = DATEADD("day",1,@NewDate)
It generates the error :
DateADD does not support the data type "DT_WSTR" for parameter number 3. The type of the parameter could not be implicity cast into a compatible type for the function .To perform this operation , the operands needs to be explicity cast with a cast operator.
So I had to cast it using :
@NewDate= DATEADD("day",1,(DT_DBDATE
And then I get : Error code 0x80020005 occured attempting to convert from data type DT_WSTR to data typr DT_DBDATE
which seems to suggest that I cant do the conversion,
does any one know if this conversion is possible and how I can do it. Obviously the @NewDate is assigned string type because thats what is available in control flow for variables for date of specific value . The datetime type in the variables window defaults to getdate() value .
I have derived the initial value of @NewDate fron an execute SQL task
Thanks
ASKER
Hello Pratima mcs,
As you can see above , I already assigned the the format you have just given me to the expression and its still returning the error as I mentioned above.
the @NewDate string is actually in the format "1999-10-11" and I thought I did every thing right but its still throwing the error.
Any ideas?
As you can see above , I already assigned the the format you have just given me to the expression and its still returning the error as I mentioned above.
the @NewDate string is actually in the format "1999-10-11" and I thought I did every thing right but its still throwing the error.
Any ideas?
if you hardcode this value will it working ?
(DT_DBDATE) "1999-10-11"
@NewDate= DATEADD("day",1,"1999-10-1
if yes then make sure that @newdate contain the date in same format
(DT_DBDATE) "1999-10-11"
@NewDate= DATEADD("day",1,"1999-10-1
if yes then make sure that @newdate contain the date in same format
ASKER
Hello Pratima mcs,
@NewDate = DATEADD("day",1, (DT_DBDATE)"1999-10-11") is working .
Then again these an additional error now : A truncation occurred during evaluation of the expression. would you know where this truncation will be resulting from . As why it is happening since @Newdate is date value in string.
Also I have used the script task to display my data value in a messageBox and it shows that it is the format - 2011-03-28 like your of 1999-10-11. So why is it not converting when the value is in a variable and not hardcoded
Thanks
@NewDate = DATEADD("day",1, (DT_DBDATE)"1999-10-11") is working .
Then again these an additional error now : A truncation occurred during evaluation of the expression. would you know where this truncation will be resulting from . As why it is happening since @Newdate is date value in string.
Also I have used the script task to display my data value in a messageBox and it shows that it is the format - 2011-03-28 like your of 1999-10-11. So why is it not converting when the value is in a variable and not hardcoded
Thanks
ASKER
This is the display of my variable.
try this hardcoded
2011-03-28
I think rpoblem with mm and dd
if above not work try
2011-28-03
then you need to change the format to
yyyy-dd-mm
2011-03-28
I think rpoblem with mm and dd
if above not work try
2011-28-03
then you need to change the format to
yyyy-dd-mm
ASKER
As you can see, my date is in the format as yyyy-dd-mm but still SSIS throws the error.
This is the error am still getting after casting the data type.:
Error at for loop container: The function "DATEADD" does not support the data type "DT_WSTR" for parameter
number 3 . 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 oerator
This is the error am still getting after casting the data type.:
Error at for loop container: The function "DATEADD" does not support the data type "DT_WSTR" for parameter
number 3 . 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 oerator
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
The suggestions I was given to resolve to issue was not helpful to resolve the issue and I had to go for a workaround which I have made available here and might be helpful to those that might have the same issue with the For Loop container in future
(DT_DBDATE) "1999-10-11"
check the sytax here
http://msdn.microsoft.com/en-us/library/ms141704.aspx