DateAdd expression works in tsql but doesn't work in ssis

Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?

dateadd("dd", -1, datediff("dd", 0, getdate()))

Thanks.

Some more info please. What do you mean by "it doesn't work"? Do you get an error or the wrong result?

If the latter, tell us what you result you get and also what result you are expecting to get.

Thanks

-Jamie

|||DateDiff returns an integer while DateAdd expects a datetime in that position. T-SQL is able to implicitly cast dates to integers, while SSIS cannot.

|||

Ok..If you run the below query in query analyzer..

select dateadd("dd", -1, datediff("dd", 0, getdate()))

it gives me.."2007-05-08 00:00:00.000". I would like to get the same value in ssis. In ssis, if I use the above as an expression for a variable, I get a design time error. "The expression for variable failed evaluation, there was an error in the expression".

Thanks for responding.

|||

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

|||

Sam_res03 wrote:

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

You'd have to use DateAdd to perform the cast from integer to date and thus define 0 as 1/1/1900 the way T-SQL does.

dateadd("dd", -1,
dateadd("dd",
datediff("dd",
dateadd("dd",0,(DT_DBDATE)"1/1/1900")
, getdate())
,(DT_DBDATE)"1/1/1900")
)

|||

Hi Jay,

Thanks for your reply. I really appreciate it. Event though your sol works, I thought I would use this instead..

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00") as this was much readable. I am sure this works for all situations.

So

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00")

gives 5/8/2007 00:00:00

and

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 23:59:59")

gives 5/8/2007 11:59 PM

I am not sure which one is efficient though, probably yours...

Thanks