Can you pass the part of WHERE clause where you use this parameter?

There is a feeling I know whats a problem.

If you're passing the parameter as varchar just convert it to datetime in SP:

where DateCol = convert(datetime, @Param, @FormatParameter)

Check the values you pass to your SP.

There must be single quote in one of parameters you pass to SP.

Did anybody tell you to get rid of dymamic SQL?

There must be something wrong with datatype conversions.

Hardcoded date is harcoded as varchar.

Supplied parameter must be datetime.

What is the datatype of column you scan for matching?

You update WHOLE table, not only rows you need.

Correct options:

UPDATE d --pemaster.dbo.tradeDaily

SET

  sm_ind = s.StarMine_Indicator_Region_100,

  sm_dt = s.as_of_date

FROM pemaster.dbo.tradeDaily AS d

LEFT JOIN pemaster.dbo.starmine_gbl_daily AS s

ON d.cusiph =...

select convert(bigint, datediff(dd, '1970-01-01', GETUTCDATE()))*24*3600 + datediff(ss, dateadd(dd, datediff(dd, 0, GETUTCDATE()), 0), GETUTCDATE())

It's an annoyance to pass a parameter but not to have a view dedicated just to display GETDATE()?

And when one day you'll need to compare dates in function not to...

Theory:

Getdate() is just datetime value. It may be supplied to UDF as a parameter. You set GETDATE() value to this parameter when you call UDF. It makes UDF deterministic and...

Can you give an example?

When you possibly need it?

UPDATE V

SET priori = toer

FROM TOEROK T INNER JOIN ventven V

ON nomat = ship

...

You are absolutely right about "hidden cursor".

But the best and most reliable way to create hidden cursor is to put table access inside UDF!

Yes, nothing is absolute, and sometimes table...

You mean in functions?

There is no case when you need GETDATE() within a UDF.

Something like this:

Code      AgentCode

190000   19XXXX

160000   16XXXX

131222   131222

131222   17XXXX

But you know the rule better, you can do it more close to the real case.

You must have the rule you've just described in table, not in code of SP.

After that you may use simple joins in SP, and rules may be added or changed...