DSXchange

You've passed the parameter inappropriately into SQL, for example in a WHERE clause where a column name is expected.

IN the SQL I have:

where UpdateDateTime > #p_MaxDate#

I suspect it needs to be quoted.

If I run it quoted

where UpdateDateTime >'#p_MaxDate#'

now I get error:

vItem_SRC,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.


I also tried

where UpdateDateTime > (select convert(datetime, '#p_MaxDate#', 111))


Same error:

vItem_SRC,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

You would, I assume, need to do an explicit date conversion in the SQL itself via some kind of TO_DATE() function. The implicit conversion it is being forced to do is failing.

Can you please tell me more about that conversion if you can ...

Not quite the same as Oracle, it would seem. See if this helps.

I tried

where UpdateDateTime >(select CONVERT(DATETIME, ('#p_MaxDate#', 111))

& tried

where UpdateDateTime >(select CAST ('#p_MaxDate#' as DATETIME))

Still same error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

Any idea ...

Get rid of the "select", it is not needed. If you can get your parameter in DD/MON/YYYY format, try: > CONVERT(DATETIME,'#p_MaxDate#') otherwise match the Style Code to the format. You picked 111 which is for a Japanese date.

I tried CONVERT(DATETIME,'#p_MaxDate#') still same error.

If I hard code the date
p_MaxDate = "2012-08-14" instead of #p_MaxDate#, the jobs works perfect.

OK... sorry but here are my problems.

One is that I don't "do SQL Server" so the nuances of handling DATETIME fields there rather than say in Oracle are not something I'm familiar with. That means I don't how implicit conversions of things like dates or timestamps are handled internally there or if/when that CONVERT function is really needed.

And second I'm not really following what all you are doing because you only give us little teaser bits with no real detail to back them up so I don't really know exactly what you are doing. So I'm left either guessing or assuming and I'm not a big fan of either.

For example, you say you tried the CONVERT as posted without any detail of what value you used in the parameter. Then you "hard code the date" but show us the parameter being set instead of what the SQL looked like after you hard coded it. Help us out here, or at least help me out. Show me what the SQL looks like that "works perfectly". Tell us what the data type of the p_MaxDate parameter is: Date or String. I suspect the former but you've never actually said.

If the job works perfectly with a hard coded date string in the SQL then there's no reason it won't still work just as perfectly if you put a Job Parameter inside those quotes in the SQL and then pass in a valid value in the exact same format. If you don't need any conversion with the value hard-coded then you won't need any conversion with the job parameter in place.

If you still are having an issue with this, please make sure you post enough detail so we can help you properly. Don't forget we're not sitting there on the same side of the glass as you, we're stuck on the other side and all we can "see" is what you describe to us. Please help us see the whole picture.