Pass date parameter

gooogle · Post by **gooogle** » Tue Aug 14, 2012 5:08 pm

In the job sequence I have :

Job1 - Creates a Location\MaxDate.txt Seq file

Job2 - Execute command activity -
Command: Type
Parameter: E:\ETL\ETL_Datasets\MaxDate.txt

Job3 - Job activity -
Parameter
p_MaxDate - EReplace(Read_MaxDate.$CommandOutput, @FM,"")

Job4 - SQL stage has #p_MaxDate#
In the job log I am getting
p_MaxDate = "2012-08-14"

But the job is failing with error:
main_program: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2012-08-14'.

Any ideas any one ?

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Aug 14, 2012 6:23 pm

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

gooogle · Post by **gooogle** » Wed Aug 15, 2012 8:51 am

IN the SQL I have:

where UpdateDateTime > #p_MaxDate#

ray.wurlod · Post by **ray.wurlod** » Wed Aug 15, 2012 2:52 pm

I suspect it needs to be quoted.

Code: Select all

WHERE UpdateDateTime > '#p_MaxDate#'

gooogle · Post by **gooogle** » Thu Aug 16, 2012 12:58 pm

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.

chulett · Post by **chulett** » Thu Aug 16, 2012 1:15 pm

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.

gooogle · Post by **gooogle** » Thu Aug 16, 2012 1:19 pm

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

chulett · Post by **chulett** » Thu Aug 16, 2012 1:36 pm

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

gooogle · Post by **gooogle** » Thu Aug 16, 2012 1:52 pm

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 ...

chulett · Post by **chulett** » Thu Aug 16, 2012 2:44 pm

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.

gooogle · Post by **gooogle** » Thu Aug 16, 2012 3:08 pm

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.

chulett · Post by **chulett** » Thu Aug 16, 2012 10:37 pm

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.

gooogle · Post by **gooogle** » Tue Aug 21, 2012 2:33 pm

SOLUTION:

THE EASIEST WAY (FOR ME) TO PASS A DATE PARAMETER

THESE JOBS GO IN A SEQUENCE

JOB1
SELECT ISNULL(MAX(Update_Dt),current_timestamp) Inserteddt FROM Table_Name
Save as MaxDate.txt Sequential file as varchar format

JOB2
Execute command activity
cat: location/MaxDate.txt

JOB3
User variable activity
(Name)
MaxDateField
(Expression)
Field(Execute command activity_name.$CommandOutput,'|',1)

JOB4
(Name)
P_MaxDate
(Value expression)
LinkName.MaxDateField
Create parameter
p_MaxDate as string