i have changed datatype of parameters from DATE to VARCHAR and now the package runs but sql task inserting NULL for all values dependent on the value of passed parameters. 

I have discovered that I used name of variable as part of Variable expression. I have removed that and am now getting error about parameter binding.
[Execute SQL Task] Error:...

Turns out, that since I was assigning DATE datatype to @FromDate parameter, it was not passing the expression to Exec SQL Query as  '2017-06-01 00:00:00.000'.  After I changed datatype...

I need to pass params in integration Services as Variables to to Exec SQL Task query where I have already set and declared parameters.   

I need  SSIS expression...

Sorry Phil, i actually need expression to return this format,'2017-06-01 00:00:00.000' .

I do realize I thought I needed 6/01/2017 12:00:00 AM format, but it's  causing ssis pkg to...

Phil, ....are you providing SQL as template for what to do in SSIS?


= (DT_DATE) DATEADD("DAY", -1,getdate())

way i see it, if i just knew...

(have same question. Since this is old post, deleted and made new one at  https://www.sqlservercentral.com/Forums/1882132/Expression-needed-for-Midnight-of-previous-day)

Would BETWEEN work?

WHERE cte_start_date BETWEEN  @FromDate AND  @ToDate

Jacob....oh I sure hope this is mundane case! Heart rate quickened... here's what I have been doing:

1rst run
SET @FromDate = '2017-04-01 00:00:00.000'
SET @ToDate =...

(deleted)

Alastair, no ofcourse not.  The sample dataset is so small that the top 1000 isn't a problem.  In real life I am not using top 1000!  i would be missing...

Yes, I added Time tbl DDL to original post.   I just finished editing my original reply.

I do have correct counts in Solution 2 and even in both...

(1)
Eirikur: I think OUTER APPLY is only way to do accomplish task because I need either a 1 or 0 for every *hour* of the day to drive the...

OK, all good, thanks everyone for help.  Appreciate people are willing to share their talent.

Very helpful guys.  Thanks everyone.

Joe, I didn't get the query because the where clause wasn't there. You gave me too much credit thinking I'd figure that out!