ODBC Output using Update Function

[CRP0021]

Hi all,
Looking for a bit of advice on an ODBC Output to a SQL table.
Here are the details:

I have a column in the the table called [Date] format is Date, Null

There are dummy records in this date field which are populated with 1900-01-01.

I would like to convert all of those dates to a NULL.

When I run a simple query in SSMS it works as expected, but when executing it from my TI it fails with the following message:
Operand type clash: date is incompatible with int

Here is the query in SSMS which is working correctly that I'd like to enable running from the Prolog of my TI process:

Update [My Table]
Set [Date] = null
Where [Date] = '1900-01-01'

Here's the TI code:
sSQL = 'UPDATE [My Table] SET [Date] = NULL WHERE [Date] = 1901-01-01';
OdbcOutput ( cODBC, sSQL );

Seems pretty simple enough but not sure where I'm going wrong. I've looked through the forums but haven't come across any posts that could help this specific case.

Thanks in advance for any advice.

[CRP0021]

I got this sorted.
Rather than delete the post I'll paste what I did here in case it's useful for anyone else.
After searching through the posts on here I initially tried the expand Function in my statement but didn't work without realizing I needed to declare a variable for the Expand function to work.
Here's what I got working:

vDate = '1900-01-01';
sSQL = EXPAND('Update [My Table] SET [Date] = NULL WHERE [Date] = ''' | vDate | '''');
OdbcOutput ( cODBC, sSQL );

[MarenC]

Hi,

Do you really need the expand function here, wouldn't we see a % sign if you were utilising it properly?

Wouldn't this equally work:

Code: Select all

sSQL = 'Update [My Table] SET [Date] = NULL WHERE [Date] = ''' | vDate | '''';

In other words, wasn't the issue that in your first attempt you didn't put the date in quotes and this is why it failed?

Maren

[lotsaram]

Clearly you don't really understand how Expand works. Becasue the way you used it is doing precisly ... nothing at all.

Expand "expands" a variable name into the value. The variable must be encased in % sign. One of the advantages of using Expand for strig concatenation is that it is a little bit more efficient to write as you don't need the pipe.

This should also work and is how you actually use Expend.

Code: Select all

vDate = '1900-01-01';
sSQL = EXPAND('Update [My Table] SET [Date] = NULL WHERE [Date] = "%vDate%"');

[CRP0021]

Thanks guys for your replies.
I followed the example from this post below.
viewtopic.php?t=9490

In any case with or without the Expand function It's working which is what I care about.
Thanks again.
Cheers!