Help with Expression Builder syntax please

Question

Hi

I am trying to create a varible with a conditional.

What it is suppose to do it check what day of the week it is and then make the date that week's Friday.

So if you check what day of the week it is and it Tuesday then add 3 days to Tuesday and you have that weeks Friday.

here is the code I am trying to use:

DATEPART( "dw", GETDATE()  ) == 3 ?  (DT_WSTR,4)Year(GETDATE()) + RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2)  +  RIGHT("0" + (DT_WSTR, 2)Day(      DATEADD( "d", 3, GETDATE() )         ),2) : GETDATE()

so what this does it If the DayoftheWeek equals 3 then then add 3 days and then output the date as yyyymmdd.

When I run this code in the Expression Builder I get this error:

TITLE: Expression Builder
------------------------------

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_DBTIMESTAMP" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of conditional operation "DATEPART("dw",GETDATE()) == 3 ? (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("d",3,GETDATE())),2) : GETDATE()" failed with error code 0xC004709F.

If I just run:

(DT_WSTR,4)Year(GETDATE()) + RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2)  +  RIGHT("0" + (DT_WSTR, 2)Day(      DATEADD( "d", 3, GETDATE() )         ),2)

It gives me the correct answer

if I run:

DATEPART( "dw", GETDATE()  ) == 3 ?  GETDATE(): GETDATE()

That also gives me the correct answer.

But as soon as I add Year:

DATEPART( "dw", GETDATE()  ) == 3 ?  YEAR( GETDATE()  ): GETDATE()

It gives the error listed above.

Please help.

Thank you

 

Answer 1

The data types "DT_WSTR" and "DT_DBTIMESTAMP" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Hello,

The TRUE part of the condition returns a different data type the the FALSE part; that's not possible, both have to return the exactly same data type. You have to choose one data type and then cast the returned value to this type.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Olaf is correct - both the "then" and "else" terms in the condition have to return compatible types.

However, your logic can be simplified a little - it doesn't need a conditional.  Use a DATEADD with a DATEPART to supply how many days to add.

To wit: DATEPART("dw", GETDATE()) will return the "day of week" as an integer - with 1 being Sunday.  Use DATEADD("d", GETDATE(), ...) to add days, either positive or negative. 

You may need a conditional based on whether you're past Friday or before...

---

Talk to me now on

Answer 3

Hi Tod,

Sorry just can't follow what you are saying. I am trying to get the Friday date into a variable so I can use that to query a database for files with that Friday date.

Could you please give more detail on how I can use datepart and dateadd to get the Friday Date?

thank you

Answer 4

(I'm assuming that if "today" is Saturday, you'll want "yesterday"...)

DATEADD("d", GETDATE(), 6 - DATEPART("dw", GETDATE()))

The expression should give you a DateTime - if you want just the date, prepend a (DT_DBDATE) cast.

---

Talk to me now on